13. Practical Exercise – Version 5

We have already written several versions of this exercise. The latest version used a layered architecture:

The [dao] layer implements the [InterfaceDao] interface. We created a class that implements this interface:

• [DaoTaxWithTaxAdminDataInJsonFile], which retrieved tax data from a JSON file.

We will implement the [InterfaceDao] interface using a new class, [DaoTaxWithTaxAdminDataInDatabase], which will retrieve data from the tax authority from a MySQL database.

13.1. Creating the [dbtaxes-2019] database

Following the example from the previous chapter, we will create a MySQL database named [dbtaxes-2019], owned by the user [admtaxes] with the password [pwdtaxes]. This database will have two tables:

• the table [tbbrackets] will contain the tax brackets;
• the table [tbconstants] will contain the constants used in tax calculation.

The PHP 7 course created the database, the user, and the tables using the [phpMyAdmin] wizard, then exported the resulting structure to a SQL file. We’ll take the reverse approach: we’ll write the SQL script [dbtaxes-2019.sql] directly, placing it in the [Data] folder of version 5:

-- database [dbtaxes-2019] of the tax calculation application - MySQL DBMS
-- to be executed by the administrator [root] of the DBMS:
--   mysql -u root < dbtaxes-2019.sql
-- the database, its tables and its user are recreated if they already exist

-- the database
DROP DATABASE IF EXISTS `dbtaxes-2019`;
CREATE DATABASE `dbtaxes-2019` CHARACTER SET utf8mb4;

-- its owner [admtaxes] with password [pwdtaxes]
DROP USER IF EXISTS 'admtaxes'@'localhost';
CREATE USER 'admtaxes'@'localhost' IDENTIFIED BY 'pwdtaxes';
GRANT ALL PRIVILEGES ON `dbtaxes-2019`.* TO 'admtaxes'@'localhost';

-- the tables are created in the [dbtaxes-2019] database
USE `dbtaxes-2019`;

-- the table of the tax brackets
CREATE TABLE tbbrackets (
    id INT NOT NULL AUTO_INCREMENT,
    limits DECIMAL(10, 2) NOT NULL,
    coeffr DECIMAL(6, 2) NOT NULL,
    coeffn DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
);

-- the table of the tax calculation constants
CREATE TABLE tbconstants (
    id INT NOT NULL AUTO_INCREMENT,
    halfShareQfCeiling DECIMAL(10, 2) NOT NULL,
    singleIncomeCeilingForReduction DECIMAL(10, 2) NOT NULL,
    coupleIncomeCeilingForReduction DECIMAL(10, 2) NOT NULL,
    halfShareReductionValue DECIMAL(10, 2) NOT NULL,
    singleDiscountCeiling DECIMAL(10, 2) NOT NULL,
    coupleDiscountCeiling DECIMAL(10, 2) NOT NULL,
    singleTaxCeilingForDiscount DECIMAL(10, 2) NOT NULL,
    coupleTaxCeilingForDiscount DECIMAL(10, 2) NOT NULL,
    tenPercentAllowanceMax DECIMAL(10, 2) NOT NULL,
    tenPercentAllowanceMin DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id)
);

Comments

• Line 7: The [dbtaxes-2019] database is deleted if it exists. The script can then be run again to regenerate the database if it has been deleted or corrupted. The database name contains a hyphen: it must be enclosed in backquotes ` so that MySQL does not interpret it as a subtraction;
• line 8: create the database with the character set [utf8mb4];
• lines 11–13: The user [admtaxes], with password [pwdtaxes], is (re)created. This user has full privileges on the database [dbtaxes-2019];
• line 16: the following commands apply to the database [dbtaxes-2019];
• Lines 19–25: The table [tbbrackets] has four columns:
  • [id] is an integer, the table’s primary key, and will be auto-incremented by the DBMS ([AUTO_INCREMENT]). This means that MySQL will manage the primary key values itself during inserts: it will assign the value 1 to the primary key of the first insert, then 2 to the next one, and so on;
  • [limits] is a 10-digit decimal number with 2 decimal places ([DECIMAL(10, 2)]). It will contain the elements from the [limits] column of the tax brackets;
  • [coeffr] is a 6-digit decimal number with 2 decimal places. It will contain the elements from column [coeffR] of the tax brackets;
  • [coeffn] is a 10-digit decimal number with 2 decimal places. It will contain the elements from column [coeffN] of the tax brackets;
• Lines 28–41: Table [tbconstants] has a primary key [id] and one decimal column per tax calculation constant. It will contain only one row.

The type [DECIMAL] stores numbers exactly, unlike the real types [FLOAT] and [DOUBLE], which store an approximated value in binary: 0.14 is stored exactly as 0.14.

Run this script in the [Laragon] terminal opened in the [version-05/Data] folder using the command:

mysql -u root < dbtaxes-2019.sql

If everything goes well, nothing is displayed. Verify the result by logging in as [admtaxes]:

> mysql -u admtaxes -ppwdtaxes dbtaxes-2019 -e "show tables"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------+
| Tables_in_dbtaxes-2019 |
+------------------------+
| tbbrackets             |
| tbconstants            |
+------------------------+

> mysql -u admtaxes -ppwdtaxes dbtaxes-2019 -e "describe tbbrackets"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------+---------------+------+-----+---------+----------------+
| Field  | Type          | Null | Key | Default | Extra          |
+--------+---------------+------+-----+---------+----------------+
| id     | int           | NO   | PRI | NULL    | auto_increment |
| limits | decimal(10,2) | NO   |     | NULL    |                |
| coeffr | decimal(6,2)  | NO   |     | NULL    |                |
| coeffn | decimal(10,2) | NO   |     | NULL    |                |
+--------+---------------+------+-----+---------+----------------+

• Line 6: The [id] column is the primary key (PRI), and its values are managed by MySQL (auto_increment).

Of course, you can also open and run the script [dbtaxes-2019.sql] in [HeidiSQL] or [phpMyAdmin] to examine the table structure there.

13.2. Code Organization

To better illustrate the role of the various PHP scripts we are writing, we will organize the code for Version 5 into folders:

version-05
├── Business
│   ├── Business.php
│   └── InterfaceBusiness.php
├── Dao
│   ├── DaoTaxWithTaxAdminDataInDatabase.php
│   ├── DaoTransferAdminDataFromJsonFile2Database.php
│   ├── InterfaceDao.php
│   ├── InterfaceDao4TransferAdminData2Database.php
│   └── TraitDao.php
├── Data
│   ├── database.json
│   ├── dbtaxes-2019.sql
│   ├── errors.json
│   ├── results.json
│   ├── taxadmindata.json
│   └── taxpayersdata.json
├── Entities
│   ├── Database.php
│   ├── TaxAdminData.php
│   ├── TaxException.php
│   └── TaxPayerData.php
├── Main
│   ├── MainCalculateTaxWithTaxAdminDataInMySQLDatabase.php
│   └── MainTransferAdminDataFromJsonFile2MySQLDatabase.php
├── Tests
│   ├── data
│   │   ├── database-incomplete.json
│   │   └── database-wrong-pwd.json
│   ├── BusinessTest.php
│   └── DaoTest.php
└── Utilities
    └── Utilities.php

• [Entities]: application entities, objects exchanged between layers;
• [Utilities]: application utilities;
• [Data]: data used or produced by the application, and the SQL database script. Here, we have decided to use only JSON files for text files. These offer several advantages:
  • they are recognized by many tools;
  • these tools support syntax highlighting. Furthermore, the JSON notation follows specific rules. When these rules are not followed, the tools flag the error. For example, a mistake that is difficult to detect in a basic text file is using the uppercase letter O instead of the number zero. If, in the JSON file, you inadvertently type ["coupleIncomeCeilingForReduction": 42O74], [VS Code] underlines the value 42O74 in red—since it is neither a number nor a string enclosed in quotes—and the [Problems] tab displays the error. The developer’s attention is drawn to the error, and they can correct it;
• [Dao]: the interfaces, traits, and classes of the [dao] layer;
• [Business]: the interface and class of the [business] layer. The PHP 7 course referred to this folder as [Métier]. We avoid accented characters in folder and file names: they cause problems when files are transferred from one system to another (ZIP archives, Git repositories, etc.);
• [Main]: the main scripts for version 5;
• [Tests]: the tests for version 5 ([PHPUnit]).

Version 5 has two distinct objectives:

• to populate the MySQL database [dbtaxes-2019] with the contents of the JSON file [Data/taxadmindata.json];
• to implement tax calculation using tax data that now comes from the MySQL database [dbtaxes-2019].

We will address these two objectives separately.

13.3. Populating the [dbtaxes-2019] database

13.3.1. Objective

The file [Data/taxadmindata.json] contains data from the tax authority. This is the version 4 file:

{
    "limits": [9964, 27519, 73779, 156244, 0],
    "coeffR": [0, 0.14, 0.3, 0.41, 0.45],
    "coeffN": [0, 1394.96, 5798, 13913.69, 20163.45],
    "halfShareQfCeiling": 1551,
    "singleIncomeCeilingForReduction": 21037,
    "coupleIncomeCeilingForReduction": 42074,
    "halfShareReductionValue": 3797,
    "singleDiscountCeiling": 1196,
    "coupleDiscountCeiling": 1970,
    "coupleTaxCeilingForDiscount": 2627,
    "singleTaxCeilingForDiscount": 1595,
    "tenPercentAllowanceMax": 12502,
    "tenPercentAllowanceMin": 437
}

Our objective is to transfer this data to the MySQL database [dbtaxes-2019] created earlier.

13.3.2. Entities

Entities are the objects exchanged between the application layers. Three of them come from version 4 and are included without any modifications:

• the exception [TaxException];
• the [readonly] [TaxAdminData] class containing tax administration data, constructed using its static methods [fromJsonFile] and [fromArray];
• the class [readonly] [TaxPayerData] containing a taxpayer’s data and the results of their tax calculation.

We are adding a fourth one, the entity [Database], which describes the database.

13.3.2.1. Why is there no base class [BaseEntity]?

The PHP 7 course introduced a parent class, [BaseEntity], at this point, from which all entities were derived. It was based on a rule: the attributes of an entity have the same names as the keys in the JSON file that it encapsulates. [BaseEntity] was then able to initialize any entity from a JSON file or an associative array using the following loop:

foreach ($arrayOfAttributes as $key => $value) {
  $this->$key = $value;
}

The entity attributes were declared as [protected] (untyped) so that the parent class could modify them, and each entity added its own getters and setters. We are not adopting this solution:

• the loop above silently creates a dynamic attribute when a key in the JSON file is misspelled. This has been deprecated since PHP 8.2, and is prohibited for [readonly] classes;
• Entities could be modified by any layer, via their setters or the [setFromArrayOfAttributes] method. Our entities are immutable: a layer cannot accidentally modify an object it received from another layer;
• the rule “same names in JSON and in the class” still applies, but PHP 8 uses it directly: the expression [new self(...$data)] converts the array keys into named arguments for the constructor (PHP 8.1). It is PHP that then verifies that no keys are missing, that none are unknown, and that each value is of the correct type. There is no longer any common code to factor out into a parent class.

Finally, version 4 has already added the [fromArray] method to the [TaxAdminData] class. We will see that this method is sufficient to construct a [TaxAdminData] object from the data read from the database. An important consequence is that the [business] layer from version 4 will be reused without modification: in the PHP 7 course, changes to the entities had required modifying the [business] layer.

13.3.2.2. The entity [Database]

The entity [Database] will be used to encapsulate the data from the following file: JSON [Data/database.json]:

{
    "dsn": "mysql:host=localhost;dbname=dbtaxes-2019;charset=utf8mb4",
    "id": "admtaxes",
    "pwd": "pwdtaxes",
    "tableBrackets": "tbbrackets",
    "colId": "id",
    "colLimits": "limits",
    "colCoeffR": "coeffr",
    "colCoeffN": "coeffn",
    "tableConstants": "tbconstants",
    "colHalfShareQfCeiling": "halfShareQfCeiling",
    "colSingleIncomeCeilingForReduction": "singleIncomeCeilingForReduction",
    "colCoupleIncomeCeilingForReduction": "coupleIncomeCeilingForReduction",
    "colHalfShareReductionValue": "halfShareReductionValue",
    "colSingleDiscountCeiling": "singleDiscountCeiling",
    "colCoupleDiscountCeiling": "coupleDiscountCeiling",
    "colSingleTaxCeilingForDiscount": "singleTaxCeilingForDiscount",
    "colCoupleTaxCeilingForDiscount": "coupleTaxCeilingForDiscount",
    "colTenPercentAllowanceMax": "tenPercentAllowanceMax",
    "colTenPercentAllowanceMin": "tenPercentAllowanceMin"
}

Its attributes describe the characteristics of the MySQL database [dbtaxes-2019]:

Attribute	Role
dsn	The database DSN
id	the database owner
pwd	their password
tableBrackets	the name of the table containing the tax brackets
colId / colLimits / colCoeffR / colCoeffN	the column names in the table [tableBrackets]
tableConstants	the name of the table containing the tax calculation constants
colHalfShareQfCeiling / colSingleIncomeCeilingForReduction / colCoupleIncomeCeilingForReduction / colHalfShareReductionValue / colSingleDiscountCeiling / colCoupleDiscountCeiling / colSingleTaxCeilingForDiscount / colCoupleTaxCeilingForDiscount / colTenPercentAllowanceMax / colTenPercentAllowanceMin	the column names in table [tableConstants] containing the tax calculation constants

Why list the table and column names when we already know them and they’re not likely to change? After the MySQL DBMS, we will use the PostgreSQL DBMS to store tax administration data. However, the column and table names in PostgreSQL do not follow the same rules as those in MySQL. We will have to use different names. This also applies to other DBMSs. If we want the code to be portable across DBMSs, it is preferable to use parameters rather than hard-coded table and column names. Compared to the PHP 7 course, we have added the attribute [colId], which is the name of the primary key column in the brackets table: we’ll need this to read the brackets back in the order in which they were inserted.

The [Database] class is as follows: [Entities/Database.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// the description of the tax data database: an immutable object
final readonly class Database
{
    // the tax calculation constants: each one has its column in the [tableConstants] table
    public const array CONSTANTS = [
        "halfShareQfCeiling",
        "singleIncomeCeilingForReduction",
        "coupleIncomeCeilingForReduction",
        "halfShareReductionValue",
        "singleDiscountCeiling",
        "coupleDiscountCeiling",
        "singleTaxCeilingForDiscount",
        "coupleTaxCeilingForDiscount",
        "tenPercentAllowanceMax",
        "tenPercentAllowanceMin",
    ];

    // private constructor: we must go through the [fromJsonFile] method
    private function __construct(
        // connection to the database
        public string $dsn,
        public string $id,
        #[\SensitiveParameter] public string $pwd,
        // the tax brackets table and its columns
        public string $tableBrackets,
        public string $colId,
        public string $colLimits,
        public string $colCoeffR,
        public string $colCoeffN,
        // the table of the calculation constants and its columns
        public string $tableConstants,
        public string $colHalfShareQfCeiling,
        public string $colSingleIncomeCeilingForReduction,
        public string $colCoupleIncomeCeilingForReduction,
        public string $colHalfShareReductionValue,
        public string $colSingleDiscountCeiling,
        public string $colCoupleDiscountCeiling,
        public string $colSingleTaxCeilingForDiscount,
        public string $colCoupleTaxCeilingForDiscount,
        public string $colTenPercentAllowanceMax,
        public string $colTenPercentAllowanceMin,
    ) {
    }

    // construction from a JSON file whose keys are the names of the attributes
    public static function fromJsonFile(string $databaseFilename): self
    {
        // may throw a [TaxException] exception
        $data = Utilities::readJsonFile($databaseFilename);
        // the JSON must be an object {...}
        if (!is_array($data) || array_is_list($data)) {
            throw new TaxException("The file [$databaseFilename] does not contain a JSON object", 20);
        }
        try {
            // the keys of the array become named arguments of the constructor
            return new self(...$data);
        } catch (\Error $ex) {
            // unknown key, missing key, value that is not a string...
            throw new TaxException("The file [$databaseFilename] is incorrect ({$ex->getMessage()})", 21, $ex);
        }
    }

    // the names of the columns of the calculation constants: [constant name => column name]
    public function constantColumns(): array
    {
        $columns = [];
        foreach (self::CONSTANTS as $constant) {
            // the column of the constant [halfShareQfCeiling] is in the attribute [colHalfShareQfCeiling]
            $columns[$constant] = $this->{"col" . ucfirst($constant)};
        }
        return $columns;
    }
}

Comments

• Line 9: Like [TaxAdminData], the class [Database] is immutable and cannot be derived;
• lines 12–23: the names of the ten tax calculation constants. These are also the names of the attributes of the [TaxAdminData] class;
• lines 26–50: the private constructor declares, by promotion, a public attribute of type [string] for each key in the [database.json] file, with the same name;
• line 30: The PHP 8.2 attribute [#[\SensitiveParameter]] marks the parameter as sensitive. If an exception is thrown while the constructor is executing, the password value will not appear in the exception’s call stack (stack trace), which is often written to log files: it is replaced there by a [SensitiveParameterValue] object;
• lines 53–68: the static method [fromJsonFile] constructs a [Database] object from a JSON file:
  • line 56: reading and decoding the JSON file are handled by the [Utilities] class, described later. These operations may throw a [TaxException] exception;
  • line 58: JSON must be an object—that is, an associative array, not a list;
  • line 63: the keys of the [$data] array become named arguments of the constructor. If a key is missing, PHP throws a [ArgumentCountError] error; if a key is unknown, an error [Error] (“Unknown named parameter”); if a value is not a string, an error [TypeError]. These three errors are derived from the class [Error];
  • Lines 64–66: The error is caught and converted to [TaxException] with code 21, while retaining the original error ([$ex]) as the preceding exception. The PHP error message, included in the message, specifies exactly what is wrong;
• lines 71–79: the method [constantColumns] returns the associative array [constant name => column name]. For the constant [halfShareQfCeiling], the column name is in the attribute [colHalfShareQfCeiling]. The function [ucfirst] capitalizes the first letter of a string, and the syntax [$this->{"col" . ucfirst($constant)}] refers to the attribute whose name is the value of the expression within the curly braces. This method will save us from having to write nearly identical code ten times in the [dao] layer.

13.3.2.3. The [Utilities] class

Version 4 used the [Utilities] class to remove the line-end character from the lines of a text file. We now only use JSON files: the [Utilities] class now handles both reading and writing JSON files [Utilities/Utilities.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// a class of utility functions: it can be neither instantiated nor derived
final class Utilities
{
    // private constructor: the class cannot be instantiated
    private function __construct()
    {
    }

    // reads a JSON file and returns its decoded content (JSON objects become associative arrays)
    public static function readJsonFile(string $fileName): mixed
    {
        // we retrieve the content of the file
        $contents = is_readable($fileName) ? file_get_contents($fileName) : false;
        if ($contents === false) {
            throw new TaxException("The file [$fileName] does not exist or cannot be read", 15);
        }
        // we decode the JSON
        try {
            return json_decode($contents, true, flags: JSON_THROW_ON_ERROR);
        } catch (\JsonException $ex) {
            throw new TaxException("The file [$fileName] does not contain valid JSON ({$ex->getMessage()})", 16, $ex);
        }
    }

    // saves a value in JSON format in a text file (created if it does not exist, overwritten otherwise)
    public static function writeJsonFile(string $fileName, mixed $data): void
    {
        // indented JSON, accented characters not escaped, floats always written with a decimal point (0.0)
        $json = json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE | JSON_PRESERVE_ZERO_FRACTION | JSON_THROW_ON_ERROR);
        if (@file_put_contents($fileName, "$json\n") === false) {
            throw new TaxException("Error while saving data to the file [$fileName]", 14);
        }
    }
}

Comments

• Lines 17–30: The [readJsonFile] method reads a JSON file and returns its decoded content, which can be of any type ([mixed]): A JSON object returns an associative array, while a JSON array returns a list. The code is the one we wrote in the [TaxAdminData::fromJsonFile] method in version 3. Errors are reported by a [TaxException] with code 15 (unreadable file) or 16 (invalid JSON);
• lines 33–40: the [writeJsonFile] method writes a value in JSON format to a text file. The [json_encode] options are combined by the [|] operator:
  • [JSON_PRETTY_PRINT] produces an indented, easy-to-read JSON;
  • [JSON_UNESCAPED_UNICODE] writes accented characters as they appear (marié) instead of their code (mari\u00e9);
  • [JSON_PRESERVE_ZERO_FRACTION] writes the real number 0.0 as 0.0 rather than 0: this distinguishes real numbers from integers in the output file;
  • [JSON_THROW_ON_ERROR] triggers a [JsonException] exception if the value cannot be encoded in JSON.

13.3.3. The [dao] layer

Now we can write the code that will transfer the data from the JSON file [taxadmindata.json] into the [tbbrackets, tbconstants] tables of the MySQL database [dbtaxes-2019]. We will use the following architecture:

The [dao] layer will implement the following [InterfaceDao4TransferAdminData2Database] interface: [Dao/InterfaceDao4TransferAdminData2Database.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// the interface of the [dao] layer that transfers the tax data into a database
interface InterfaceDao4TransferAdminData2Database
{
    // saves the data of the tax administration in the database
    // throws a [TaxException] exception in case of error
    public function transferAdminData2Database(): void;
}

• Line 13: The [transferAdminData2Database] method is responsible for storing tax administration data in a database.

The [InterfaceDao4TransferAdminData2Database] interface will be implemented by the following [DaoTransferAdminDataFromJsonFile2Database] class: [Dao/DaoTransferAdminDataFromJsonFile2Database.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// transfer of the data of the tax administration from a JSON file to a database
class DaoTransferAdminDataFromJsonFile2Database implements InterfaceDao4TransferAdminData2Database
{
    // the description of the target database
    private readonly Database $database;
    // the data of the tax administration
    private readonly TaxAdminData $taxAdminData;

    // the constructor
    public function __construct(string $databaseFilename, string $taxAdminDataFilename)
    {
        // may throw a [TaxException] exception
        $this->database = Database::fromJsonFile($databaseFilename);
        $this->taxAdminData = TaxAdminData::fromJsonFile($taxAdminDataFilename);
    }

    // transfers the tax data into the database
    #[\Override]
    public function transferAdminData2Database(): void
    {
        $database = $this->database;
        try {
            // we open the connection to the database
            $connection = \PDO::connect($database->dsn, $database->id, $database->pwd);
            // we start a transaction
            $connection->beginTransaction();
            // we fill the tax brackets table
            $this->fillTableBrackets($connection);
            // we fill the constants table
            $this->fillTableConstants($connection);
            // we end the transaction with a success
            $connection->commit();
        } catch (\PDOException $ex) {
            // is there a transaction in progress?
            if (isset($connection) && $connection->inTransaction()) {
                // we end the transaction with a failure
                $connection->rollBack();
            }
            // we pass the error up to the calling code as a [TaxException] exception
            throw new TaxException("Database access error ({$ex->getMessage()})", 30, $ex);
        }
        // the connection is closed when the variable [$connection] disappears, at the end of the method
    }

Comments

Here, we are applying what we learned in the chapter on MySQL.

• Line 9: The [DaoTransferAdminDataFromJsonFile2Database] class implements the [InterfaceDao4TransferAdminData2Database] interface;
• line 12: the attribute [$database] is an object of type [Database] that encapsulates the data from the file [database.json];
• line 14: the attribute [$taxAdminData] is an object of type [TaxAdminData] that encapsulates the data from the file [taxadmindata.json];
• lines 17–22: The constructor takes the names of the [database.json, taxadmindata.json] files as parameters and initializes the two attributes. If one of the files is invalid, a [TaxException] exception is thrown to the calling code;
• line 26: the single method of the [InterfaceDao4TransferAdminData2Database] interface is implemented;
• line 31: the database connection is opened. Note the notation [\PDO], which refers to the class [PDO] of PHP. In fact, since we are in the [Application] namespace, if we simply wrote [PDO], this relative name would be prefixed with the current namespace and would therefore refer to the class [Application\PDO], which does not exist. The method [PDO::connect] returns an object of type [Pdo\Mysql] here, since DSN begins with [mysql:]. Nothing in this class depends on MySQL: it is DSN—read from the [database.json] file—that determines the DBMS used;
• Lines 33–39: The [tbbrackets, tbconstants] tables are populated within a transaction. The methods [fillTableBrackets] and [fillTableConstants] trigger a [PDOException] as soon as something goes wrong. In this case, execution continues in the [catch] clause, and the database remains in its initial state: there is no risk of having a filled [tbbrackets] table and an empty [tbconstants] table;
• Lines 40–47: Case where an exception was thrown by the code. If a transaction is in progress, it is rolled back. Then an exception of type [TaxException], with code 30, is thrown, along with the message from the original exception, which becomes the preceding exception. The calling code thus only has to handle one exception type;
• line 49: the PHP 7 procedure closed the connection within a [finally] clause using the [$connection = NULL] statement. This is unnecessary: the local variable [$connection] disappears at the end of the method, and with it, the connection.

The code for the [fillTableBrackets] method is as follows:

    // filling the tax brackets table
    private function fillTableBrackets(\PDO $connection): void
    {
        // shortcuts
        $database = $this->database;
        $taxAdminData = $this->taxAdminData;
        // we empty the table in case there is something in it
        $connection->exec("delete from $database->tableBrackets");
        // we prepare the insertions
        $statement = $connection->prepare(
            "insert into $database->tableBrackets ($database->colLimits, $database->colCoeffR, $database->colCoeffN)"
            . " values (:limits, :coeffR, :coeffN)"
        );
        // we execute the prepared statement with the values of each tax bracket
        foreach ($taxAdminData->limits as $i => $limit) {
            $statement->execute([
                "limits" => $limit,
                "coeffR" => $taxAdminData->coeffR[$i],
                "coeffN" => $taxAdminData->coeffN[$i],
            ]);
        }
    }

Comments

• Line 53: The [fillTableBrackets] method receives an open connection as a parameter. We also know that a transaction has started within this connection;
• Lines 56–57: Two shortcuts to avoid having to write [$this->database] and [$this->taxAdminData] throughout the code. These are copies of object references, not copies of the objects themselves;
• line 59: the current contents of the "brackets" table are deleted. The table name is inserted into the string using the syntax ["$database->tableBrackets"]: PHP replaces the expression with the value of the attribute;
• Lines 61–64: The insertion of rows into the table is prepared. Here, the table and column names provided by the attribute [$database] are used. Table and column names cannot be parameters of a prepared statement; only values can be. This is why they are inserted into the text of the SQL statement. They come from a configuration file and not from a user: there is no risk of SQL injection;
• Lines 66–72: The prepared insertion statement is executed as many times as necessary, once per tax bracket. [$i] is the bracket number, which serves as an index in the tables [coeffR] and [coeffN].

The method code for [fillTableConstants] is as follows:

    // filling the constants table
    private function fillTableConstants(\PDO $connection): void
    {
        // shortcut
        $database = $this->database;
        // we empty the table in case there is something in it
        $connection->exec("delete from $database->tableConstants");
        // [constant name => column name]
        $columns = $database->constantColumns();
        // the parameters of the prepared statement bear the names of the constants: :halfShareQfCeiling, ...
        $parameters = array_map(fn(string $constant): string => ":$constant", array_keys($columns));
        $statement = $connection->prepare(
            "insert into $database->tableConstants (" . implode(", ", $columns) . ")"
            . " values (" . implode(", ", $parameters) . ")"
        );
        // the values of the parameters: [constant name => value of the constant]
        $values = [];
        foreach (array_keys($columns) as $constant) {
            $values[$constant] = $this->taxAdminData->$constant;
        }
        // we execute the prepared statement: a single row is inserted
        $statement->execute($values);
    }

Comments

• Line 76: The method [fillTableConstants] also receives an open connection in which a transaction is in progress;
• Line 81: The table [tbconstants] is cleared;
• line 83: the array [constant name => column name], for example, [["halfShareQfCeiling" => "halfShareQfCeiling", ...]];
• line 85: the names of the parameters for the prepared statement: [[":halfShareQfCeiling", ...]]. These are the names of the constants preceded by a colon;
• lines 86–89: preparation of the SQL insertion command. The function [implode] constructs the list of columns and the list of parameters, separated by commas. This results in the command [insert into tbconstants (halfShareQfCeiling, ...) values (:halfShareQfCeiling, ...)]. The PHP 7 course wrote this entire command, column by column, over about twenty lines;
• lines 91–94: the table of parameter values for [constant name => value]. The syntax [$this->taxAdminData->$constant] specifies the attribute of the object [TaxAdminData], whose name is the value of the variable [$constant];
• Line 96: Execution of the insert statement. There is only one row to insert.

13.3.4. The main script

The main script [Main/MainTransferAdminDataFromJsonFile2MySQLDatabase.php] relies on the layer [dao] to perform the data transfer:

<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// namespace
namespace Application;

// inclusion of the interfaces and classes needed for the transfer
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Utilities/Utilities.php";
require_once __DIR__ . "/../Dao/InterfaceDao4TransferAdminData2Database.php";
require_once __DIR__ . "/../Dao/DaoTransferAdminDataFromJsonFile2Database.php";

// definition of the constants
const DATABASE_CONFIG_FILENAME = __DIR__ . "/../Data/database.json";
const TAXADMINDATA_FILENAME = __DIR__ . "/../Data/taxadmindata.json";

try {
    // creation of the [dao] layer
    $dao = new DaoTransferAdminDataFromJsonFile2Database(DATABASE_CONFIG_FILENAME, TAXADMINDATA_FILENAME);
    // transfer of the data into the database
    $dao->transferAdminData2Database();
    print "The data of the tax administration have been transferred into the database\n";
} catch (TaxException $ex) {
    // we display the error
    print "Error no. {$ex->getCode()}: {$ex->getMessage()}\n";
}
// end
print "Done\n";

Comments

• Lines 10–15: Load the classes and interfaces required for the transfer. The paths are relative to the script’s folder ([Main]): [__DIR__ . "/../Entities"] refers to the adjacent folder [Entities];
• Lines 18–19: The paths to the two JSON files, which are also relative to the script folder. The PHP 7 course used paths relative to the current folder (["../Data/database.json"]): the script only worked if it was run from the [Main] folder;
• line 23: the [dao] layer is instantiated by passing the names of the two JSON files to the constructor;
• line 25: the data transfer is performed;
• lines 27–29: the exception code and message are displayed. The PHP 7 course used the function [utf8_encode] here, which has been deprecated since PHP 8.2 and is unnecessary since all our messages are in UTF-8.

When we run this script in a terminal opened in the [Main] folder, we get the following result:

> php MainTransferAdminDataFromJsonFile2MySQLDatabase.php
The data of the tax administration have been transferred into the database
Done

and the following content in the database:

> mysql -u admtaxes -ppwdtaxes dbtaxes-2019 -e "select * from tbbrackets"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-----------+--------+----------+
| id | limits    | coeffr | coeffn   |
+----+-----------+--------+----------+
|  1 |   9964.00 |   0.00 |     0.00 |
|  2 |  27519.00 |   0.14 |  1394.96 |
|  3 |  73779.00 |   0.30 |  5798.00 |
|  4 | 156244.00 |   0.41 | 13913.69 |
|  5 |      0.00 |   0.45 | 20163.45 |
+----+-----------+--------+----------+

• column [id]: the values assigned by MySQL to the primary key. Numbering starts at 1. If we run the script again, the rows are deleted and then reinserted, and MySQL continues the numbering (6, 7…): an auto-increment key value is never reused;
• the numbers have two decimal places, in accordance with the [DECIMAL(10, 2)] data type of the columns.

The option [\G] at the end of the SQL command displays each row vertically, one column per screen line, which is easier to read for a table with many columns:

> mysql -u admtaxes -ppwdtaxes dbtaxes-2019 -e "select * from tbconstants\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
                             id: 1
             halfShareQfCeiling: 1551.00
singleIncomeCeilingForReduction: 21037.00
coupleIncomeCeilingForReduction: 42074.00
        halfShareReductionValue: 3797.00
          singleDiscountCeiling: 1196.00
          coupleDiscountCeiling: 1970.00
    singleTaxCeilingForDiscount: 1595.00
    coupleTaxCeilingForDiscount: 2627.00
         tenPercentAllowanceMax: 12502.00
         tenPercentAllowanceMin: 437.00

13.4. Tax Calculation

13.4.1. Architecture

Version 4 of the tax calculation application used a layered architecture:

The [dao] layer implements the [InterfaceDao] interface. The [DaoTaxWithTaxAdminDataInJsonFile] class in version 4 retrieved tax data from a JSON file. We will implement the [InterfaceDao] interface using a new class, [DaoTaxWithTaxAdminDataInDatabase], which will retrieve data from the tax authority from the MySQL database. The [dao] layer, as before, will write the results and errors to text files and retrieve taxpayer data from a text file as well. Only this time, these text files will be JSON files. Furthermore, we know that if we continue to adhere to the [InterfaceDao] interface, the [business] layer will not need to be modified.

13.4.2. The [TaxPayerData] entity

The [TaxPayerData] class in version 4 encapsulates the data for a taxpayer ([married, children, salary]) and the results of their tax calculation ([tax, surcharge, discount, reduction, rate]). It is carried over as-is. The taxpayer data is now in the following JSON file [Data/taxpayersdata.json]:

[
    {
        "married": "yes",
        "children": 2,
        "salary": 55555
    },
    {
        "married": "yesx",
        "children": "2x",
        "salary": "55555x"
    },
    {
        "married": "yes",
        "children": "2",
        "salary": 50000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50000
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100000
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100000
    },
    {
        "married": "yes",
        "children": 5,
        "salary": 100000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 100000
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 30000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 200000
    }
]

The keys for the JSON objects are the names of the attributes in the [TaxPayerData] class. Note the following:

• line 8: taxpayer #1 (numbers start at 0) is incorrect: all three of their data entries are invalid;
• line 14: the number of children for taxpayer No. 2 is given as a string. We will accept it;
• the other eleven taxpayers are those from the tax administration’s official tests, which have been used since version 1.

13.4.3. The [dao] layer

13.4.3.1. The [InterfaceDao] interface

The [InterfaceDao] interface from version 4 is included without modification [Dao/InterfaceDao.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// the interface of the [dao] layer
// all the methods throw a [TaxException] exception in case of an unrecoverable error
interface InterfaceDao
{
    // reading the taxpayers data
    // the incorrect lines are reported in the text file [$errorsFilename]
    /** @return TaxPayerData[] */
    public function getTaxPayersData(string $taxPayersFilename, string $errorsFilename): array;

    // reading the data of the tax administration (tax brackets and constants)
    public function getTaxAdminData(): TaxAdminData;

    // saving the results
    /** @param TaxPayerData[] $taxPayersData */
    public function saveResults(string $resultsFilename, array $taxPayersData): void;
}

The methods [getTaxPayersData] and [saveResults] accept filenames: there is no indication that these are basic text files or JSON files. The interface therefore remains valid.

13.4.3.2. The [TraitDao] trait

The [TraitDao] trait implements the [getTaxPayersData] and [saveResults] methods of the [InterfaceDao] interface. Since we’ve switched from basic text files to JSON files, we need to rewrite it as [Dao/TraitDao.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// implementation of the methods of [InterfaceDao] that work with JSON files
trait TraitDao
{
    // reading the taxpayers data in a JSON file
    #[\Override]
    public function getTaxPayersData(string $taxPayersFilename, string $errorsFilename): array
    {
        // the content of the file: an array of objects {married, children, salary}
        // may throw a [TaxException] exception
        $taxpayers = Utilities::readJsonFile($taxPayersFilename);
        if (!is_array($taxpayers) || !array_is_list($taxpayers)) {
            throw new TaxException("The file [$taxPayersFilename] does not contain a JSON array", 17);
        }
        // array of the taxpayers data
        $taxPayersData = [];
        // array of the errors
        $errors = [];
        // we check each of the taxpayers
        foreach ($taxpayers as $number => $taxpayer) {
            $taxpayerErrors = $this->check($taxpayer);
            if ($taxpayerErrors === []) {
                // one more taxpayer
                $taxPayersData[] = new TaxPayerData(
                    strtolower(trim($taxpayer["married"])),
                    (int) $taxpayer["children"],
                    (int) $taxpayer["salary"]
                );
            } else {
                // one more error: we note the number of the incorrect taxpayer in the JSON array
                $errors[] = ["number" => $number, "errors" => $taxpayerErrors];
            }
        }
        // we save the errors in a JSON file
        Utilities::writeJsonFile($errorsFilename, $errors);
        // result of the function
        return $taxPayersData;
    }

    // checking the data of a taxpayer: returns the list of the incorrect attributes
    private function check(mixed $taxpayer): array
    {
        // the taxpayer must be a JSON object
        if (!is_array($taxpayer)) {
            return [["taxpayer" => $taxpayer]];
        }
        $errors = [];
        // the marital status must be yes or no
        $married = $taxpayer["married"] ?? null;
        if (!is_string($married) || !in_array(strtolower(trim($married)), ["yes", "no"], true)) {
            $errors[] = ["married" => $married];
        }
        // the number of children and the salary must be positive or zero integers
        // given as a number (2) or as a string ("2")
        foreach (["children", "salary"] as $key) {
            $value = $taxpayer[$key] ?? null;
            $valid = (is_int($value) && $value >= 0)
                || (is_string($value) && preg_match('/^\s*\d+\s*$/', $value) === 1);
            if (!$valid) {
                $errors[] = [$key => $value];
            }
        }
        return $errors;
    }

    // saving the results in a JSON file
    #[\Override]
    public function saveResults(string $resultsFilename, array $taxPayersData): void
    {
        // the [TaxPayerData] objects are turned into JSON objects from their public attributes
        Utilities::writeJsonFile($resultsFilename, $taxPayersData);
    }
}

Comments

• Lines 13–44: The [getTaxPayersData] method takes the following parameters:
  • [$taxPayersFilename]: the name of the JSON file containing taxpayer data [taxpayersdata.json];
  • [$errorsFilename]: the name of the JSON file containing the [errors.json] errors;
• Line 17: The contents of the JSON file containing taxpayer data are decoded into the [$taxpayers] array. If the JSON file proves to be unusable, a [TaxException] exception is thrown;
• line 18: the content must be a JSON array, i.e., a PHP list. Otherwise, a code 17 exception is thrown;
• line 22: the array [$taxPayersData] will contain taxpayer data encapsulated in objects of type [TaxPayerData];
• line 24: errors will be accumulated in the array [$errors];
• lines 26–39: construction of the array [$taxPayersData]. Before being encapsulated in a [TaxPayerData] object, the data is validated by the [check] method, which returns a list of invalid attributes—or an empty list if the data is valid:
  • Lines 30–34: Case where the data is valid. A new [TaxPayerData] object is created and added to the [$taxPayersData] array. The marital status is converted to lowercase, and the number of children and the salary are converted to integers: [(int) "2"] is set to 2;
  • Line 37: Cases where the data is invalid. The error message includes the number of the incorrect taxpayer in the JSON array so that the user can locate it, and then the error is added to the array [$errors];
• Line 41: The errors encountered are logged in the JSON file [$errorsFilename]. The PHP 7 course was incorrectly writing a JSON string, one per line: with multiple errors, the file was not a valid JSON file. We write a JSON array of the errors;
• line 43: we return the array of constructed [TaxPayerData] objects: this was the method’s purpose;
• lines 47–70: the private method [check] verifies the validity of a taxpayer’s data. The parameter [$taxpayer] is of type [mixed]: an element of the JSON array can be anything (an object, a number, a string, etc.). The method aggregates the invalid attributes into the [$errors] array in the form of [incorrect attribute => value] arrays:
  • line 50: the element must be a JSON object;
  • line 55: the [??] operator (null coalescing) returns the value of [$taxpayer["married"]] if it exists and is not [null], and [null] otherwise. This avoids the warning that would be triggered by a missing key;
  • lines 56–58: the marital status must be a string representing “yes” or “no,” ignoring case and spaces;
  • lines 61–68: the number of children and the salary must be positive integers or zero, given in the form of a JSON number (2) or a string ("2"). A string must consist of digits, optionally surrounded by spaces: this is what the regular expression [/^\s*\d+\s*$/] checks for. Note the comparison [preg_match(...) === 1]: the function [preg_match] returns 1 if the string matches the pattern, 0 otherwise, and [false] in case of an error;
• Lines 74–78: the implementation of the [saveResults] method of the [InterfaceDao] interface. The array of [TaxPayerData] objects is directly stored in the JSON format: the function [json_encode] converts an object into a JSON object whose keys are the names of its public attributes. The PHP 7 course constructed the JSON string from the array “by hand,” by concatenating the JSON strings of the objects separated by commas and adding square brackets.

13.4.3.3. The [DaoTaxWithTaxAdminDataInDatabase] class

The [DaoTaxWithTaxAdminDataInDatabase] class implements the [InterfaceDao] interface as follows: [Dao/DaoTaxWithTaxAdminDataInDatabase.php]:

<?php

declare(strict_types=1);

// namespace
namespace Application;

// a [dao] layer that finds the data of the tax administration in a database
class DaoTaxWithTaxAdminDataInDatabase implements InterfaceDao
{
    // the trait implements the [getTaxPayersData] and [saveResults] methods of the interface
    use TraitDao;

    // the description of the database
    private readonly Database $database;
    // the object of type [TaxAdminData] that contains the data of the tax administration
    private readonly TaxAdminData $taxAdminData;

    // the constructor
    public function __construct(string $databaseFilename)
    {
        // the description of the database - may throw a [TaxException] exception
        $this->database = Database::fromJsonFile($databaseFilename);
        $database = $this->database;
        try {
            // we open the connection to the database
            // the rows read will be returned as associative arrays [column => value]
            $connection = \PDO::connect(
                $database->dsn,
                $database->id,
                $database->pwd,
                [\PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC]
            );
            // we start a transaction
            $connection->beginTransaction();
            // we read the tax brackets table
            $brackets = $this->getBrackets($connection);
            // we read the constants table
            $constants = $this->getConstants($connection);
            // we end the transaction with a success
            $connection->commit();
        } catch (\PDOException $ex) {
            // is there a transaction in progress?
            if (isset($connection) && $connection->inTransaction()) {
                // we end the transaction with a failure
                $connection->rollBack();
            }
            // we pass the error up to the calling code as a [TaxException] exception
            throw new TaxException("Database access error ({$ex->getMessage()})", 30, $ex);
        }
        // building the [TaxAdminData] object: the data read are checked
        $this->taxAdminData = TaxAdminData::fromArray([...$brackets, ...$constants]);
    }

Comments

• line 6: We retain the namespace already used for the other classes in the application;
• line 9: the class [DaoTaxWithTaxAdminDataInDatabase] implements the interface [InterfaceDao];
• line 12: the [TraitDao] trait is imported. We know that this trait implements part of the interface. The only method left to implement is the [getTaxAdminData] method, which simply returns the private attribute [$taxAdminData]. We can infer that the constructor will need to initialize this attribute. That is its sole purpose;
• line 20: the constructor receives as its only parameter the JSON filename [database.json], which defines the MySQL database [dbtaxes-2019];
• Line 23: The JSON file is used to create an object of type [Database], which is stored in the attribute [$database]. If the JSON file could not be processed correctly, a [TaxException] exception is thrown;
• lines 28–33: the database connection is opened. The fourth parameter sets the default mode for reading rows: associative arrays of type [column => value];
• line 35: a transaction is started. This is not strictly necessary because only two SQL commands will be executed, and these commands do not modify the database. However, it is done to isolate the program from other database users: both tables are read in the same state, even if another program modifies them at the same time;
• Line 37: The tax bracket table [tbbrackets] is read using the private method [getBrackets]. This method returns the array [["limits" => [...], "coeffR" => [...], "coeffN" => [...]]];
• line 39: The calculation constants table [tbconstants] is read by the private method [getConstants]. It returns the array [constant name => value];
• Line 41: If we reach this line, it means everything went well. We therefore commit the transaction;
• lines 42–49: If we reach this point, it means an exception has occurred. We therefore roll back the transaction if one was in progress. Then, to ensure consistent exceptions, we rethrow the error as an exception of type [TaxException] with code 30;
• line 52: the expression [[...$brackets, ...$constants]] merges the two associative arrays into a single one, which has exactly the thirteen keys expected by the method [TaxAdminData::fromArray]. This method verifies the data read from the database, just as it verified the data from the JSON file, and converts it to real numbers. The PHP 7 course initialized the [TaxAdminData] object attribute by attribute, with ten calls to setters.

The [getBrackets] method is as follows:

    // reading the tax brackets table
    // returns the array ["limits" => [...], "coeffR" => [...], "coeffN" => [...]]
    private function getBrackets(\PDO $connection): array
    {
        // shortcut
        $database = $this->database;
        // we prepare then execute the SELECT query
        $statement = $connection->prepare(
            "select $database->colLimits, $database->colCoeffR, $database->colCoeffN"
            . " from $database->tableBrackets order by $database->colId"
        );
        $statement->execute();
        // all the rows at once: an array of associative arrays
        $brackets = $statement->fetchAll();
        // we extract each column of the result
        return [
            "limits" => array_column($brackets, $database->colLimits),
            "coeffR" => array_column($brackets, $database->colCoeffR),
            "coeffN" => array_column($brackets, $database->colCoeffN),
        ];
    }

Comments

• line 57: the method receives as a parameter an open connection in which a transaction is in progress;
• Lines 62–65: The SELECT statement is prepared and then executed. The [order by] clause on the primary key ensures that the brackets are read back in the order in which they were inserted. The PHP 7 command did not have this clause and relied on the order in which MySQL returned the rows, which is not guaranteed;
• line 68: all rows of the result are read at once. This yields an array of associative arrays, for example, [[["limits" => "9964.00", "coeffr" => "0.00", "coeffn" => "0.00"], ...]]. Note that the values in the columns of type [DECIMAL] are returned as character strings: PHP does not have an exact decimal type, and the driver does not convert them to [float], which would result in a loss of precision. The [TaxAdminData::fromArray] method will perform the conversion;
• Lines 70–74: The function [array_column] extracts a column from an array of associative arrays: [array_column($brackets, "limits")] returns [["9964.00", "27519.00", ...]].

The private method [getConstants] is as follows:

    // reading the table of the calculation constants
    // returns the array [constant name => value]
    private function getConstants(\PDO $connection): array
    {
        // [constant name => column name]
        $columns = $this->database->constantColumns();
        // we prepare then execute the SELECT query
        $statement = $connection->prepare(
            "select " . implode(", ", $columns) . " from {$this->database->tableConstants}"
        );
        $statement->execute();
        // we use the result - only 1 row here - [false] if the table is empty
        $row = $statement->fetch() ?: [];
        // [constant name => value of its column] (null if the table is empty)
        return array_map(fn(string $column): mixed => $row[$column] ?? null, $columns);
    }

Comments

• line 82: the array [constant name => column name];
• lines 84–86: the SELECT command for the ten columns is prepared and then executed. Note the curly braces in [{$this->database->tableConstants}]: without them, PHP would interpret only [$this->database] from the string;
• Line 89: The table has only one row, read by the [fetch] method. This method returns [false] if there is no row. The operator [?:] returns its left operand if it evaluates to true, and its right operand otherwise: [$row] therefore evaluates to an empty array if the table is empty;
• line 91: for each constant, we look up the value of its column in [$row]. The function [array_map], when applied to a single array, preserves its keys: we obtain the array [constant name => value]. If the table is empty, all values are [null], and the [TaxAdminData::fromArray] method will report an invalid value.

Note that the class does not depend on the MySQL DBMS. It is the [database.json] file that sets the DBMS used, via the DSN of the database.

13.4.4. The [business] layer

• We have just implemented the [dao] layer [3];
• because we adhered to the [InterfaceDao] interface, the [business] layer [2] can remain unchanged. The PHP 7 course, however, had to modify it because it had also changed the entities exchanged between the layers: the [executeBatchTaxes] method used the setters of the [TaxPayerData] class from version 4, which had been removed. Here, the entities have not changed.

The [InterfaceBusiness] interface and the [Business] class from version 4 are therefore included without any modifications in the [Business] folder. Recall the [executeBatchTaxes] method:

    // calculation of the taxes in batch mode
    // --------------------------------------------------------------------------
    #[\Override]
    public function executeBatchTaxes(string $taxPayersFileName, string $resultsFileName, string $errorsFileName): void
    {
        // we let the exceptions coming from the [dao] layer go up
        // we retrieve the taxpayers data
        $taxPayersData = $this->dao->getTaxPayersData($taxPayersFileName, $errorsFileName);
        // we calculate the tax of each taxpayer
        $results = array_map(
            fn(TaxPayerData $taxPayerData): TaxPayerData => $taxPayerData->withResults(
                $this->calculateTax($taxPayerData->married, $taxPayerData->children, $taxPayerData->salary)
            ),
            $taxPayersData
        );
        // saving the results
        $this->dao->saveResults($resultsFileName, $results);
    }
}

• line 192: the [business] layer is unaware that taxpayer data now comes from a JSON file: it requests the data from the [dao] layer and receives an array of [TaxPayerData] objects, as in version 4;
• Line 201: Similarly, it is unaware that the results are saved in JSON format.

13.4.5. The main script

• We have just implemented the [dao] [3] and [business] [2] layers;
• we still need to write the main script [1].

The main script [Main/MainCalculateTaxWithTaxAdminDataInMySQLDatabase.php] is similar to the one in version 4:

<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// namespace
namespace Application;

// inclusion of the interfaces and classes
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/TaxPayerData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Utilities/Utilities.php";
require_once __DIR__ . "/../Dao/InterfaceDao.php";
require_once __DIR__ . "/../Dao/TraitDao.php";
require_once __DIR__ . "/../Dao/DaoTaxWithTaxAdminDataInDatabase.php";
require_once __DIR__ . "/../Business/InterfaceBusiness.php";
require_once __DIR__ . "/../Business/Business.php";

// definition of the constants
const DATABASE_CONFIG_FILENAME = __DIR__ . "/../Data/database.json";
const TAXPAYERSDATA_FILENAME = __DIR__ . "/../Data/taxpayersdata.json";
const RESULTS_FILENAME = __DIR__ . "/../Data/results.json";
const ERRORS_FILENAME = __DIR__ . "/../Data/errors.json";

try {
    // creation of the [dao] layer
    $dao = new DaoTaxWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
    // creation of the [business] layer
    $business = new Business($dao);
    // tax calculation in batch mode
    $business->executeBatchTaxes(TAXPAYERSDATA_FILENAME, RESULTS_FILENAME, ERRORS_FILENAME);
    print "The results have been saved in the file [results.json]\n";
} catch (TaxException $ex) {
    // we display the error
    print "Error no. {$ex->getCode()}: {$ex->getMessage()}\n";
}
// end
print "Done\n";

Comments

• lines 10–19: loading all the files needed to calculate the tax;
• lines 22–25: the names of the various JSON files in the application;
• line 29: construction of the [dao] layer;
• line 31: construction of the [business] layer;
• line 33: call to the [executeBatchTaxes] method of the [business] layer.

Results

> php MainCalculateTaxWithTaxAdminDataInMySQLDatabase.php
The results have been saved in the file [results.json]
Done

The application generates two JSON files in the [Data] folder:

• [results.json]: the results of the various tax calculations;
• [errors.json]: which reports the errors found in the JSON file [taxpayersdata.json].

The [errors.json] file is as follows:

[
    {
        "number": 1,
        "errors": [
            {
                "married": "yesx"
            },
            {
                "children": "2x"
            },
            {
                "salary": "55555x"
            }
        ]
    }
]

This means that in [taxpayersdata.json], entry #1 in the taxpayer table is incorrect, and explains why. The results file [results.json] is as follows:

[
    {
        "married": "yes",
        "children": 2,
        "salary": 55555,
        "tax": 2814,
        "surcharge": 0,
        "discount": 0,
        "reduction": 0,
        "rate": 0.14
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 50000,
        "tax": 1384,
        "surcharge": 0,
        "discount": 384,
        "reduction": 347,
        "rate": 0.14
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50000,
        "tax": 0,
        "surcharge": 0,
        "discount": 720,
        "reduction": 0,
        "rate": 0.14
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100000,
        "tax": 19884,
        "surcharge": 4480,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100000,
        "tax": 16782,
        "surcharge": 7176,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100000,
        "tax": 9200,
        "surcharge": 2180,
        "discount": 0,
        "reduction": 0,
        "rate": 0.3
    },
    {
        "married": "yes",
        "children": 5,
        "salary": 100000,
        "tax": 4230,
        "surcharge": 0,
        "discount": 0,
        "reduction": 0,
        "rate": 0.14
    },
    {
        "married": "no",
        "children": 0,
        "salary": 100000,
        "tax": 22986,
        "surcharge": 0,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 30000,
        "tax": 0,
        "surcharge": 0,
        "discount": 0,
        "reduction": 0,
        "rate": 0.0
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200000,
        "tax": 64210,
        "surcharge": 7498,
        "discount": 0,
        "reduction": 0,
        "rate": 0.45
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 200000,
        "tax": 42842,
        "surcharge": 17283,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    }
]

Note that taxpayer No. 2 in the file [taxpayersdata.json], whose number of children was the string "2," has 2 children listed in the results (line 14): the string was converted to an integer by the [dao] layer. These results are consistent with those of version 4.

13.5. [PHPUnit] Tests

As we did for version 4, we will write [PHPUnit] tests for version 5. They are in the [version-05/Tests] folder.

13.5.1. [dao] Layer Test

The [Tests/DaoTest.php] test is as follows:

<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// namespace of the tests
namespace Application\Tests;

// the tested classes
use Application\DaoTaxWithTaxAdminDataInDatabase;
use Application\DaoTransferAdminDataFromJsonFile2Database;
use Application\TaxException;
use Application\InterfaceDao;
use Application\TaxAdminData;
use Application\TaxPayerData;
// the PHPUnit classes
use PHPUnit\Framework\Attributes\DataProvider;
use PHPUnit\Framework\TestCase;

// loading the classes of the [dao] layer
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/TaxPayerData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Utilities/Utilities.php";
require_once __DIR__ . "/../Dao/InterfaceDao.php";
require_once __DIR__ . "/../Dao/TraitDao.php";
require_once __DIR__ . "/../Dao/DaoTaxWithTaxAdminDataInDatabase.php";
require_once __DIR__ . "/../Dao/InterfaceDao4TransferAdminData2Database.php";
require_once __DIR__ . "/../Dao/DaoTransferAdminDataFromJsonFile2Database.php";

final class DaoTest extends TestCase
{
    // the data folder of version 5
    private const string DATA = __DIR__ . "/../Data";
    // the tested [dao] layer
    private InterfaceDao $dao;
    // a temporary file for the errors
    private string $errorsFilename;

    // executed before each test
    #[\Override]
    protected function setUp(): void
    {
        // creation of the [dao] layer
        $this->dao = new DaoTaxWithTaxAdminDataInDatabase(self::DATA . "/database.json");
        // a temporary file
        $this->errorsFilename = tempnam(sys_get_temp_dir(), "errors");
    }

    // executed after each test
    #[\Override]
    protected function tearDown(): void
    {
        // deletion of the temporary file
        unlink($this->errorsFilename);
    }

    // the data of the tax administration read from the database
    public function testTaxAdminData(): void
    {
        $taxAdminData = $this->dao->getTaxAdminData();
        // calculation constants
        $this->assertSame(1551.0, $taxAdminData->halfShareQfCeiling);
        $this->assertSame(21037.0, $taxAdminData->singleIncomeCeilingForReduction);
        $this->assertSame(42074.0, $taxAdminData->coupleIncomeCeilingForReduction);
        $this->assertSame(3797.0, $taxAdminData->halfShareReductionValue);
        $this->assertSame(1196.0, $taxAdminData->singleDiscountCeiling);
        $this->assertSame(1970.0, $taxAdminData->coupleDiscountCeiling);
        $this->assertSame(1595.0, $taxAdminData->singleTaxCeilingForDiscount);
        $this->assertSame(2627.0, $taxAdminData->coupleTaxCeilingForDiscount);
        $this->assertSame(12502.0, $taxAdminData->tenPercentAllowanceMax);
        $this->assertSame(437.0, $taxAdminData->tenPercentAllowanceMin);
        // tax brackets
        $this->assertSame([9964.0, 27519.0, 73779.0, 156244.0, 0.0], $taxAdminData->limits);
        $this->assertSame([0.0, 0.14, 0.30, 0.41, 0.45], $taxAdminData->coeffR);
        $this->assertSame([0.0, 1394.96, 5798.0, 13913.69, 20163.45], $taxAdminData->coeffN);
    }

    // the transfer of the JSON file to the database, then the rereading of the database
    public function testTransfer(): void
    {
        // transfer
        (new DaoTransferAdminDataFromJsonFile2Database(self::DATA . "/database.json", self::DATA . "/taxadmindata.json"))
            ->transferAdminData2Database();
        // rereading of the database by a new [dao] layer
        $dao = new DaoTaxWithTaxAdminDataInDatabase(self::DATA . "/database.json");
        // the database contains the data of the JSON file
        $this->assertEquals(TaxAdminData::fromJsonFile(self::DATA . "/taxadmindata.json"), $dao->getTaxAdminData());
    }

    // incorrect descriptions of the database
    public static function invalidDatabases(): array
    {
        // [file name, expected error code]
        return [
            "non-existent file" => ["nonexistent.json", 15],
            "missing key" => ["database-incomplete.json", 21],
            "wrong password" => ["database-wrong-pwd.json", 30],
        ];
    }

    #[DataProvider("invalidDatabases")]
    public function testInvalidDatabases(string $file, int $code): void
    {
        // we expect a [TaxException] exception with a specific code
        $this->expectException(TaxException::class);
        $this->expectExceptionCode($code);
        // construction of a [dao] layer with an incorrect database description
        new DaoTaxWithTaxAdminDataInDatabase(__DIR__ . "/data/$file");
    }

    // the data of the taxpayers read from a JSON file
    public function testTaxPayersData(): void
    {
        $taxPayersData = $this->dao->getTaxPayersData(self::DATA . "/taxpayersdata.json", $this->errorsFilename);
        // 11 correct taxpayers
        $this->assertCount(11, $taxPayersData);
        $this->assertContainsOnlyInstancesOf(TaxPayerData::class, $taxPayersData);
        // the first and the last one
        $this->assertEquals(new TaxPayerData("yes", 2, 55555), $taxPayersData[0]);
        $this->assertEquals(new TaxPayerData("yes", 3, 200000), $taxPayersData[10]);
        // the taxpayer no. 1 of the JSON file is incorrect
        $errors = json_decode(file_get_contents($this->errorsFilename), true);
        $this->assertSame([
            ["number" => 1, "errors" => [["married" => "yesx"], ["children" => "2x"], ["salary" => "55555x"]]],
        ], $errors);
    }

    // a non-existent taxpayers file
    public function testNonExistentTaxPayersData(): void
    {
        $this->expectException(TaxException::class);
        $this->expectExceptionCode(15);
        $this->dao->getTaxPayersData(self::DATA . "/nonexistent.json", $this->errorsFilename);
    }
}

Comments

• lines 21–30: definition of the test environment. We load the files for the two [dao] layers from version 5: the transfer layer and the tax calculation layer;
• lines 43–49: Before each test, we construct the [dao] layer using the [Data/database.json] file, just as the main script does;
• line 60: The [testTaxAdminData] method is identical to that in version 4. However, this time, the data being tested was read from the database;
• Lines 81–90: We test the transfer of the JSON file to the database, then read the database again using a new layer, [dao]. The resulting [TaxAdminData] object must be equal ([assertEquals]) to the one constructed from the JSON file. This test assumes that the transfer successfully loaded the data from the file [taxadmindata.json] into the database: it does not modify the data expected by the other tests;
• lines 93–101: three incorrect database descriptions: a nonexistent file (code 15), a file named [database-incomplete.json] that is missing the key [colCoeffN] (code 21), and a file named [database-wrong-pwd.json] with an incorrect password (code 30). The last two files are in the [Tests/data] folder;
• lines 104–111: we verify that a layer named [dao], created with an incorrect description, throws a [TaxException] exception with the correct error code;
• lines 114–128: reading taxpayer data from the JSON file. We verify the number of taxpayers retrieved and the contents of the first and last records, then the exact contents of the error file;
• Line 131: A nonexistent taxpayer file should trigger a code 15 exception.

13.5.2. Test of the [business] layer

The [Tests/BusinessTest.php] test corresponds to version 4. Only the test environment changes:

<?php

// strict respect of the declared types of function parameters and results
declare(strict_types=1);

// namespace of the tests
namespace Application\Tests;

// the tested classes
use Application\DaoTaxWithTaxAdminDataInDatabase;
use Application\InterfaceBusiness;
use Application\Business;
// the PHPUnit classes
use PHPUnit\Framework\Attributes\DataProvider;
use PHPUnit\Framework\TestCase;

// loading the classes of the [dao] and [business] layers
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/TaxPayerData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Utilities/Utilities.php";
require_once __DIR__ . "/../Dao/InterfaceDao.php";
require_once __DIR__ . "/../Dao/TraitDao.php";
require_once __DIR__ . "/../Dao/DaoTaxWithTaxAdminDataInDatabase.php";
require_once __DIR__ . "/../Business/InterfaceBusiness.php";
require_once __DIR__ . "/../Business/Business.php";

final class BusinessTest extends TestCase
{
    // the data folder of version 5
    private const string DATA = __DIR__ . "/../Data";
    // the tested [business] layer
    private InterfaceBusiness $business;

    // executed before each test
    #[\Override]
    protected function setUp(): void
    {
        // creation of the [dao] layer
        $dao = new DaoTaxWithTaxAdminDataInDatabase(self::DATA . "/database.json");
        // creation of the [business] layer
        $this->business = new Business($dao);
    }

• Line 10: the class of the [dao] layer used;
• lines 18–27: the files loaded, those from the main script [MainCalculateTaxWithTaxAdminDataInMySQLDatabase];
• line 41: the layer [business] is built on top of a layer [dao] that reads the database.

The data provider [taxpayers] and the method [testCalculateTax] are those from version 4.

13.5.3. Running the Tests

The MySQL DBMS must be started ([Laragon] / Start All). The tests are run in a terminal opened in the [php8-oct-2026] folder:

> php vendor/bin/phpunit scripts-console/tax/version-05/Tests --testdox
PHPUnit 12.5.37 by Sebastian Bergmann and contributors.

Runtime:       PHP 8.5.11
Configuration: .../php8-oct-2026/phpunit.xml

..................                                                18 / 18 (100%)

Time: 00:00.035, Memory: 16.00 MB

Business (Application\Tests\Business)
 ✔ Calculate tax with data set "couple, 2 children, 55555"
 ✔ Calculate tax with data set "couple, 2 children, 50000"
 ✔ Calculate tax with data set "couple, 3 children, 50000"
 ✔ Calculate tax with data set "single, 2 children, 100000"
 ✔ Calculate tax with data set "single, 3 children, 100000"
 ✔ Calculate tax with data set "couple, 3 children, 100000"
 ✔ Calculate tax with data set "couple, 5 children, 100000"
 ✔ Calculate tax with data set "single, 0 children, 100000"
 ✔ Calculate tax with data set "couple, 2 children, 30000"
 ✔ Calculate tax with data set "single, 0 children, 200000"
 ✔ Calculate tax with data set "couple, 3 children, 200000"

Dao (Application\Tests\Dao)
 ✔ Tax admin data
 ✔ Transfer
 ✔ Invalid databases with data set "non-existent file"
 ✔ Invalid databases with data set "missing key"
 ✔ Invalid databases with data set "wrong password"
 ✔ Tax payers data
 ✔ Non existent tax payers data

OK (18 tests, 49 assertions)

All 18 tests pass: 7 tests from the [dao] layer (including 3 for the [invalidDatabases] provider) and 11 tests from the [business] layer.