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 an [InterfaceDao] interface. We built a class implementing this interface:

• [DaoImpotsWithTaxAdminDataInJsonFile], which retrieved tax data from a JSON file;

We will implement the [InterfaceDao] interface with a new class [DaoImpotsWithTaxAdminDataInDatabase] that will retrieve tax administration data from a MySQL database.

13.1. Creating the [dbimpots-2019] database

Following the example in the "link" section, we create a MySQL database named [dbimpots-2019], owned by [admimpots] with the password [mdpimpots]:

• In [1-4] above, we see the database [dbimpots-2019], which currently has no tables;

• In [1-5] above, we see that the user [admimpots] has full privileges on the [dbimpots-2019] database. What we don’t see here is that this user has the password [admimpots];

We will now create the table [tbtranches], which will contain the tax brackets:

• In [1-7], we create a table named [tbtranches] with 4 columns;

• In [3-6], we define a column named [id] (3), of type integer [int] (4), which will be the primary key [6] of the table and will be auto-incremented [5] by the DBMS. This means that MySQL will manage the primary key values itself during insertions. It will assign the value 1 to the primary key of the first insertion, then 2 to the next, and so on;
• in [7], the wizard offers additional configuration options for the primary key. Here, we simply accept [7] the default values;

• In [8-16], we define the other three columns of the table:
  • [limits] (8), a decimal number (9) with 10 digits, including 2 decimal places (10), will contain the elements of column 17 of the tax brackets;
  • [coeffR] (11), a 6-digit decimal number (12) with 2 decimal places (13), will contain the values for column 18 of the tax brackets;
  • [coeffN] (14) of type decimal number (15) with 10 digits, including 2 decimal places (16), will contain the elements of column 19 of the tax brackets;

After validating this structure, we obtain the following result:

• in [5], the key icon indicates that the [id] column is the primary key. We can also see that this primary key has integer values (6) and is managed (auto-incremented) by MySQL;

In the same way we created the [tbtranches] table, we build the [tbconstantes] table, which will contain the constants used in tax calculation:

It is possible to export the database structure to a text file as a sequence of SQL statements:

Option [5] exports only the database structure here, not its content. In our case, the database does not yet have any content.

Option [11] generates the following SQL file [dbimpots-2019.sql]:

-- phpMyAdmin SQL Dump
-- version 4.8.5
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: June 30, 2019 at 1:10 PM
-- Server version: 5.7.24
-- PHP Version: 7.2.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dbimpots-2019`
--
CREATE DATABASE IF NOT EXISTS `dbimpots-2019` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `dbimpots-2019`;

-- --------------------------------------------------------

--
-- Table structure for table `tbconstantes`
--

DROP TABLE IF EXISTS `tbconstantes`;
CREATE TABLE `tbconstantes` (
  `id` int(11) NOT NULL,
  `QfDemiPartLimit` decimal(10,2) NOT NULL,
  `singleIncomeLimitForReduction` decimal(10,2) NOT NULL,
  `coupleIncomeLimitForReduction` decimal(10,2) NOT NULL,
  `half-share-reduction-value` decimal(10,2) NOT NULL,
  `singleDiscountLimit` decimal(10,2) NOT NULL,
  `coupleDiscountLimit` decimal(10,2) NOT NULL,
  `singleTaxCeilingForDiscount` decimal(10,2) NOT NULL,
  `coupleTaxCeilingForDiscount` decimal(10,2) NOT NULL,
  `MaxTenPercentDeduction` decimal(10,2) NOT NULL,
  `minTenPercentDeduction` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `tbtranches`
--

DROP TABLE IF EXISTS `tbtranches`;
CREATE TABLE `tbtranches` (
  `id` int(11) NOT NULL,
  `limits` decimal(10,2) NOT NULL,
  `coeffR` decimal(10,2) NOT NULL,
  `coeffN` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tbconstantes`
--
ALTER TABLE `tbconstantes`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `tbtranches`
--
ALTER TABLE `tbtranches`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tbconstantes`
--
ALTER TABLE `tbconstantes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `tbtranches`
--
ALTER TABLE `tbtranches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

You can use this SQL file to regenerate the [dbimpots-2019] database if it has been destroyed or corrupted. There is no need to delete the database before regenerating it, as the SQL script handles this automatically:

13.2. Code Organization

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

• in [1], overview of version 05;
• in [2], the application entities, entities exchanged between layers;
• in [3], the application utilities;
• in [4], data used or produced by the application. 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, JSON has strict rules. When these rules are not followed, the tools flag them. For example, a common error in a basic text file is using uppercase or lowercase O instead of zeros. If this error occurs, it will be flagged. In the JSON code:

"plafondRevenusCouplePourReduction": 42O74

where a capital O was inadvertently used instead of a zero in [42074], NetBeans flags the error:

In fact, NetBeans recognizes the uppercase O, which makes [49O74] a string. It concludes that the syntax should be [4-5]: the string [47O74] should be enclosed in quotes. The developer’s attention is thus drawn to the error and can correct it: either by adding quotation marks or by replacing the O with a zero;

The other elements of version 05 are as follows:

• in [6], the interfaces and classes of the [Dao] layer;
• in [7], the interfaces and classes of the [business] layer;
• in [8], the main scripts of version 05;

Version 05 has two distinct objectives:

• to populate the MySQL database [dbimpots-2019] with the contents of the JSON file [Data/txadmindata.json];
• to implement tax calculation using tax data now sourced from the MySQL database [dbimpots-2019];

We will address these two objectives separately.

13.3. Filling the database [dbimpots-2019]

13.3.1. Objective

The text file taxadmindata.json contains data from the tax authority:

{
    "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
    ],
    "half-share-income-limit": 1551,
    "IncomeLimitSingleForReduction": 21037,
    "coupleIncomeLimitForReduction": 42074,
    "half-share-reduction-value": 3797,
    "single-person-discount-limit": 1196,
    "coupleTaxDeductionLimit": 1970,
    "taxCeilingCoupleForDeduction": 2627,
    "taxLimitSingleForDiscount": 1595,
    "MaxTenPercentDeduction": 12502,
    "minimumTenPercentDeduction": 437
}

Our goal is to transfer this data into the MySQL database [dbimpots-2019] created earlier.

13.3.2. The entities

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

{
    "dsn": "mysql:host=localhost;dbname=dbimpots-2019",
    "id": "admimpots",
    "pwd": "mdpimpots",
    "tableTranches": "tbtranches",
    "colLimites": "limites",
    "colCoeffR": "coeffr",
    "colCoeffN": "coeffn",
    "tableConstants": "tbconstants",
    "colHalfShareLimit": "halfShareLimit",
    "colIncomeCeilingSingleForReduction": "IncomeCeilingSingleForReduction",
    "colIncomeLimitCoupleForReduction": "incomeLimitCoupleForReduction",
    "colHalfShareReductionValue": "halfShareReductionValue",
    "colSingleDiscountCeiling": "singleDiscountCeiling",
    "colCoupleDiscountLimit": "coupleDiscountLimit",
    "colSingleTaxCeilingForDiscount": "singleTaxCeilingForDiscount",
    "colCoupleTaxLimitForDeduction": "coupleTaxLimitForDeduction",
    "colMaxTenPercentDeduction": "MaxTenPercentDeduction",
    "colMinimumTenPercentDeduction": "minimumTenPercentDeduction"
}

The [TaxAdminData] entity will be used to encapsulate the data from the following JSON file [taxadmindata.json]:

{
    "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
    ],
    "half-share-income-limit": 1551,
    "IncomeLimitSingleForReduction": 21037,
    "coupleIncomeLimitForReduction": 42074,
    "half-share-reduction-value": 3797,
    "singleDiscountLimit": 1196,
    "coupleDiscountLimit": 1970,
    "coupleTaxCeilingForDiscount": 2627,
    "singleTaxCeilingForDiscount": 1595,
    "MaxTenPercentDeduction": 12502,
    "minimumTenPercentDeduction": 437
}

The [TaxPayerData] entity will be used to encapsulate the data from the following JSON file [taxpayerdata.json]:

[
    {
        "married": "yes",
        "children": 2,
        "salary": 55555
    },
    {
        "married": "yes",
        "children": "2x",
        "salary": "55555x"
    },
    {
        "married": "yes",
        "children": "2",
        "salary": 50000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50000
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 5,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 30000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 20,000
    }
]

13.3.2.1. The base class [BaseEntity]

To simplify the entity code, we will adopt the following rule: the attributes of an entity have the same names as the attributes in the JSON file that the entity is intended to encapsulate. Based on this rule, the entities [Database, TaxAdminData, TaxPayerData] share common features that can be factored into a parent class. This will be the following [BaseEntity] class:

<?php

namespace Application;

class BaseEntity {
  // attribute
  protected $arrayOfAttributes;

  // Initialization from a JSON file
  public function setFromJsonFile(string $jsonFilename) {
    // retrieve the contents of the tax data file
    $fileContents = \file_get_contents($jsonFilename);
    $error = FALSE;
    // error?
    if (!$fileContents) {
      // log the error
      $error = TRUE;
      $message = "The data file [$jsonFilename] does not exist";
    }
    if (!$error) {
      // retrieve the JSON code from the configuration file into an associative array
      $this->arrayOfAttributes = \json_decode($fileContents, true);
      // error?
      if ($this->arrayOfAttributes === FALSE) {
        // log the error
        $error = TRUE;
        $message = "The JSON data file [$jsonFilename] could not be processed correctly";
      }
    }
    // error?
    if ($error) {
      // throw an exception
      throw new TaxException($message);
    }
    // initialize the class attributes
    foreach ($this->arrayOfAttributes as $key => $value) {
      $this->$key = $value;
    }
    // return the object
    return $this;
  }

  public function checkForAllAttributes() {
    // check that all keys have been initialized
    foreach (\array_keys($this->arrayOfAttributes) as $key) {
      if ($key !== "arrayOfAttributes" && !isset($this->$key)) {
        throw new ExceptionImpots("The [$key] attribute of the class "
          . get_class($this) . " has not been initialized");
      }
    }
  }

  public function setFromArrayOfAttributes(array $arrayOfAttributes) {
    // initialize certain class attributes
    foreach ($arrayOfAttributes as $key => $value) {
      $this->$key = $value;
    }
    // return the object
    return $this;
  }

  // toString
  public function __toString() {
    // object attributes
    $arrayOfAttributes = \get_object_vars($this);
    // remove the parent class attribute
    unset($arrayOfAttributes["arrayOfAttributes"]);
    // JSON string of the object
    return \json_encode($arrayOfAttributes, JSON_UNESCAPED_UNICODE);
  }

  // getter
  public function getArrayOfAttributes() {
    return $this->arrayOfAttributes;
  }

}

Comments

• line 5: the [BaseEntity] class is intended to be extended by the [Database, TaxAdminData, TaxPayerData] classes;
• line 7: the [$arrayOfAttributes] attribute is an array containing all the attributes of the child class that extends [BaseEntity] along with their values;
• lines 9–41: the [$arrayOfAttributes] attribute is initialized from the JSON file [$jsonFilename] passed as a parameter. An [ExceptionImpot] exception is thrown if the JSON file could not be read or if it is not a valid JSON file;
• lines 36–38: this is special code if executed by a child class. In this case, [$this] represents an instance of the child class [Database, TaxAdminData, TaxPayerData], and in this scenario, lines 36–38 initialize the attributes of this child class, provided that these attributes have protected (or public) visibility (see linked section). We noted that the attributes of the entities [Database, TaxAdminData, TaxPayerData] are the same as the attributes of the JSON file they encapsulate. Finally, the [setFromJsonFile] method allows a child class to initialize itself from a JSON file;
• line 40: the object [$this] is set to a child class instance if the [setFromJsonFile] method was called by a child class;
• lines 43–51: the [checkForAllAttributes] method allows a child class to verify that all its attributes have been initialized. If this is not the case, an [ExceptionImpots] exception is thrown. This method allows the child class to verify that its JSON file has not omitted certain attributes;
• lines 53–60: The [setFromArrayOfAttributes] method allows a child class to initialize all or some of its attributes from an associative array whose keys have the same names as the attributes of the child class to be initialized;
• lines 63–70: The [__toString] method provides the JSON representation of a child class;

13.3.2.2. The [Database] entity

The [Database] entity is as follows:

<?php

namespace Application;

class Database extends BaseEntity {
  // attributes
  protected $dsn;
  protected $id;
  protected $pwd;
  protected $tableTranches;
  protected $colLimits;
  protected $colCoeffR;
  protected $colCoeffN;
  protected $constantTable;
  protected $colHalfShareCeiling;
  protected $colIncomeLimitForSingleTaxpayersForDeduction;
  protected $colIncomeLimitForCoupleForReduction;
  protected $colHalfShareReductionValue;
  protected $colIncomeCeilingForSingleTaxDeduction;
  protected $colIncomeCeilingForCoupleDiscount;
  protected $colIncomeCeilingSingleForDeduction;
  protected $colCoupleTaxCeilingForDeduction;
  protected $colMaxTenPercentDeduction;
  protected $colMinTenPercentDeduction;

 …

}

The [Database] class is used to encapsulate the data from the following JSON file [database.json]:

{
    "dsn": "mysql:host=localhost;dbname=dbimpots-2019",
    "id": "admimpots",
    "pwd": "mdpimpots",
    "tableTranches": "tbtranches",
    "colLimites": "limites",
    "colCoeffR": "coeffr",
    "colCoeffN": "coeffn",
    "tableConstants": "tbconstants",
    "colHalfShareLimit": "halfShareLimit",
    "colIncomeCeilingSingleForReduction": "IncomeCeilingSingleForReduction",
    "colIncomeLimitCoupleForReduction": "incomeLimitCoupleForReduction",
    "colHalfShareReductionValue": "halfShareReductionValue",
    "colSingleDiscountCeiling": "singleDiscountCeiling",
    "colCoupleDiscountLimit": "coupleDiscountLimit",
    "colSingleTaxCeilingForDiscount": "singleTaxCeilingForDiscount",
    "colCoupleTaxLimitForDeduction": "coupleTaxLimitForDeduction",
    "colMaxTenPercentDeduction": "MaxTenPercentDeduction",
    "colMinimumTenPercentDeduction": "minimumTenPercentDeduction"
}

The class and the JSON file have the same attributes. These describe the characteristics of the MySQL database [dbimpots-2019]:

dsn	Database DSN name
id	Database owner
pwd	Their password
tableTranches	Name of the table containing the tax brackets
colLimits colRate colCoeffN	Column names in the [tableTranches] table
tableConstants	Name of the table containing the tax calculation constants
colIncomeCeilingForHalfShare colIncomeLimitSingleForReduction colIncomeLimitCoupleForReduction colHalfShareReductionValue colSingleTaxCreditLimit colCoupleDeductionCeiling colIncomeCeilingSingleForDeduction colTaxCeilingCoupleForDeduction colMaxTenPercentDeduction colMinTenPercentDeduction	Column names in the [tableConstants] table containing tax calculation constants

Why name the tables and columns when we already know their names and they are not likely to change? After the MySQL DBMS, we will use the PostgreSQL DBMS to store tax administration data. However, Postgres column and table names do not follow the same rules as MySQL. We will be forced to use different names. This is also true for other DBMSs. If we want code that is portable across DBMSs, it is preferable to use parameters rather than hard-coded table and column names.

Let’s return to the code for the [Database] class:

<?php

namespace Application;

class Database extends BaseEntity {
  // attributes
  protected $dsn;
  protected $id;
  protected $pwd;
  protected $tableTranches;
  protected $colLimits;
  protected $colCoeffR;
  protected $colCoeffN;
  protected $constantTable;
  protected $colHalfShareIncomeLimit;
  protected $colIncomeLimitSingleForDeduction;
  protected $colIncomeLimitCoupleForReduction;
  protected $colHalfShareReductionValue;
  protected $colSingleDiscountLimit;
  protected $colCoupleDiscountLimit;
  protected $colIncomeCeilingSingleForDeduction;
  protected $colCoupleTaxCeilingForDeduction;
  protected $colMaxTenPercentDeduction;
  protected $minTenPercentDiscount;

  // setter
  // initialization
  public function setFromJsonFile(string $jsonFilename) {
    // parent
    parent::setFromJsonFile($jsonFilename);
    // check that all attributes have been initialized
    parent::checkForAllAttributes();
    // return the object
    return $this;
  }

  // getters and setters
  public function getDsn() {
    return $this->dsn;
  }

  …

  public function setDsn($dsn) {
    $this->dsn = $dsn;
    return $this;
  }

  …

}

Comments

• lines 7–24: all class attributes have [protected] visibility. This is a requirement for them to be modifiable from the parent class [BaseEntity] (see linked section);
• lines 28–35: the [setFromJsonFile] method allows the attributes of the [Database] class to be initialized from the contents of a JSON file passed as a parameter. The attributes in the JSON file and those in the [Database] class must be identical. If the JSON file is unusable, an exception is thrown;
• line 30: the parent class performs the initialization;
• line 32: the parent class is asked to verify that all attributes of the [Database] class have been initialized. If this is not the case, an exception is thrown;
• line 34: the [Database] instance that has just been initialized is returned;
• lines 37 and beyond: the getters and setters for the class’s attributes;

13.3.2.3. The [TaxAdminData] entity

The [TaxAdminData] entity is as follows:

<?php

namespace Application;

class TaxAdminData extends BaseEntity {
  // tax brackets
  protected $limits;
  protected $coeffR;
  protected $coeffN;
  // tax calculation constants
  protected $halfShareIncomeLimit;
  protected $singleIncomeThresholdForReduction;
  protected $coupleIncomeLimitForReduction;
  protected $half-share-reduction-value;
  protected $singleDiscountLimit;
  protected $coupleDiscountCeiling;
  protected $coupleTaxCeilingForDeduction;
  protected $singleTaxCeilingForDeduction;
  protected $MaxTenPercentDeduction;
  protected $minimumTenPercentDeduction;

  …
}

The [TaxAdminData] class is used to encapsulate the data from the following JSON file [taxadmindata.json]:

{
    "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
    ],
    "half-share-income-limit": 1551,
    "IncomeLimitSingleForReduction": 21037,
    "coupleIncomeLimitForReduction": 42074,
    "half-share-reduction-value": 3797,
    "singleDiscountLimit": 1196,
    "coupleDiscountLimit": 1970,
    "coupleTaxCeilingForDiscount": 2627,
    "singleTaxCeilingForDiscount": 1595,
    "10PercentMaxDeduction": 12502,
    "TenPercentMinDeduction": 437
}

The class and the JSON file have the same attributes. These represent the tax administration data. The rest of the code for the [TaxAdminData] class is as follows:

<?php

namespace Application;

class TaxAdminData extends BaseEntity {
  // tax brackets
  protected $limits;
  protected $coeffR;
  protected $coeffN;
  // tax calculation constants
  protected $halfShareIncomeLimit;
  protected $singleIncomeLimitForReduction;
  protected $coupleIncomeLimitForReduction;
  protected $half-share-reduction-value;
  protected $singleDiscountCeiling;
  protected $coupleDiscountLimit;
  protected $coupleTaxCeilingForDiscount;
  protected $singleTaxCeilingForDeduction;
  protected $MaxTenPercentDeduction;
  protected $minimumTenPercentDeduction;

  // initialization
  public function setFromJsonFile(string $taxAdminDataFilename) {
    // parent
    parent::setFromJsonFile($taxAdminDataFilename);
    // check that all attributes have been initialized
    parent::checkForAllAttributes();
    // check that the attribute values are real numbers >= 0
    foreach ($this as $key => $value) {
      if ($key !== "arrayOfAttributes") {
        // $value must be a real number >= 0 or an array of real numbers >= 0
        $result = $this->check($value);
        // error?
        if ($result->error) {
          // throw an exception
          throw new ExceptionImpots("The value of the [$key] attribute is invalid");
        } else {
          // store the value
          $this->$key = $result->value;
        }
      }
    }
    // return the object
    return $this;
  }

  protected function check($value): \stdClass {
    // $value is an array of string elements or a single element
    if (!\is_array($value)) {
      $array = [$value];
    } else {
      $array = $value;
    }
    // Convert the array of strings to an array of real numbers
    $newArray = [];
    $result = new \stdClass();
    // the elements of the array must be positive or zero decimal numbers
    $pattern = '/^\s*([+]?)\s*(\d+\.\d*|\.\d+|\d+)\s*$/';
    for ($i = 0; $i < count($array); $i++) {
      if (preg_match($pattern, $array[$i])) {
        // add the float to newArray
        $newArray[] = (float) $array[$i];
      } else {
        // log the error
        $result->error = TRUE;
        // exit
        return $result;
      }
    }
    // return the result
    $result->error = FALSE;
    if (!\is_array($value)) {
      // a single value
      $result->value = $newArray[0];
    } else {
      // a list of values
      $result->value = $newArray;
    }
    return $result;
  }

  // getters and setters
 …
}

Comments

• Line 23: The [setFromJsonFile] method is used to initialize the attributes of the [TaxAdminData] class from a JSON file passed as a parameter. The attributes in the JSON file must have the same names as those in the class;
• Line 25: The parent class performs this task;
• line 27: the parent class is asked to verify that all attributes of the child class have been initialized;
• lines 29–42: we verify locally that all attributes have a positive real value or are null. This verification was already discussed in the “link” section of version 03;

13.3.3. The [dao] layer

Now we can write the code that will transfer data from the text file [taxadmindata.json] into the tables [tbtranches, tbconstantes] of the MySQL database [dbimpots-2019]. We will adopt the following architecture:

The [dao] layer will implement the following [InterfaceDao4TransferAdminDataFromFile2Database] interface:

<?php

// namespace
namespace Application;

interface InterfaceDao4TransferAdminData2Database {

  public function transferAdminData2Database(): void;
}

Comments

• line 8: the [transferAdminData2Database] method is responsible for storing tax administration data in a database;

The [InterfaceDao4TransferAdminData2Database] interface will be implemented by the following [DaoTransferAdminDataFromJsonFile2Database] class:

<?php

// namespace
namespace Application;

// definition of a TransferAdminDataFromFile2DatabaseDao class
class DaoTransferAdminDataFromJsonFile2Database implements InterfaceDao4TransferAdminData2Database {
  // attributes of the target database
  private $database;
  // tax administration data
  private $taxAdminData;

  // constructor
  public function __construct(string $databaseFilename, string $taxAdminDataFilename) {
    // store the database configuration
    $this->database = (new Database())->setFromJsonFile($databaseFilename);
    // load tax data
    $this->taxAdminData = (new TaxAdminData())->setFromJsonFile($taxAdminDataFilename);
  }

  // Transfer tax bracket data from a text file
  // to the database
  public function transferAdminData2Database(): void {
    // we work with the database
    $database = $this->database;
    try {
      // Open the database connection
      $connection = new \PDO($database->getDsn(), $database->getId(), $database->getPwd());
      // We want an exception to be thrown for every DBMS error
      $connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
      // Start a transaction
      $connection->beginTransaction();
      // we populate the tax bracket table
      $this->fillTableTranches($connection);
      // fill the constants table
      $this->fillTableConstants($connection);
      // commit the transaction
      $connection->commit();
    } catch (\PDOException $ex) {
      // Is there a transaction in progress?
      if (isset($connection) && $connection->inTransaction()) {
        // roll back the transaction
        $connection->rollBack();
      }
      // throw the exception to the calling code
      throw new ExceptionImpots($ex->getMessage());
    } finally {
      // close the connection
      $connection = NULL;
    }
  }


  // populate the tax bracket table
  private function fillTableTranches($connection): void {
    …
  }

  // Fill the constants table
  private function fillConstantsTable($connection): void {
    …
  }

}

Comments

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

• line 7: the class [DaoTransferAdminDataFromJsonFile2Database] implements the interface [InterfaceDao4TransferAdminData2Database];
• line 9: the attribute [$database] is the object of type [Database] encapsulating the data from the [database.json] file;
• line 11: the attribute [$taxAdminData] is an object of type [TaxAdminData] encapsulating the data from the file [taxadmindata.json];
• lines 14–19: the constructor receives the names of the files [database.json, taxadmindata.json] as parameters;
• line 16: initialization of the [$database] attribute;
• line 18: initialization of the [$taxAdminData] attribute;
• line 23: the single method of the [InterfaceDao4TransferAdminData2Database] interface is implemented;
• Lines 26–38: The tables [tbtranches] and [tbconstantes] are populated in two steps:
  • line 34: First, the table [tbtranches] is filled. This is done within a transaction (lines 32, 38). The method [fillTableTranches] (line 55) throws an exception as soon as something goes wrong. In this case, execution continues with the catch / finally block in lines 39–50;
  • line 36: the table [tbconstantes] is filled in the same way using the method [fillTableConstantes] (line 60);
• lines 39–47: case where an exception was thrown by the code;
• lines 41–44: if a transaction exists, it is rolled back;
• line 46: an exception of type [ExceptionImpots] is thrown with the message of the original exception, which is of any type;
• lines 47–50: in the [finally] clause, the connection is closed;

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

private function fillTableTranches($connection): void {
    // shortcut for the database
    $database = $this->database;
    // data to be inserted into the database
    $limits = $this->taxAdminData->getLimits();
    $coeffR = $this->taxAdminData->getCoeffR();
    $coeffN = $this->taxAdminData->getCoeffN();
    // clear the table in case there is anything in it
    $statement = $connection->prepare("delete from " . $database->getTableTranches());
    $statement->execute();
    // prepare the inserts
    $sqlInsert = "insert into {$database->getTableTranches()} "
      . "({$database->getColLimites()}, {$database->getColCoeffR()},"
      . " {$database->getColCoeffN()}) values (:limits, :coeffR, :coeffN)";
    $statement = $connection->prepare($sqlInsert);
    // execute the prepared statement with the tax bracket values
    for ($i = 0; $i < count($limits); $i++) {
      $statement->execute([
        "limits" => $limits[$i],
        "coeffR" => $coeffR[$i],
        "coeffN" => $coeffN[$i]]);
    }
  }

Comments

• line 1: the [fillTableTranches] method takes an open connection as a parameter. We also know that a transaction has started within this connection;
• lines 5–7: The values to be inserted into the table are provided by the [$taxAdminData] attribute;
• lines 9–10: the current contents of the [tbtranches] table are cleared;
• lines 12–15: We prepare to insert rows into the table. Here, we use the column names provided by the [$database] attribute;
• lines 17–22: the insertion statement prepared in lines 12–15 is executed as many times as necessary;

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

private function fillTableConstants($connection): void {
    // shortcut
    $database = $this->database;
    // clear the table in case there is anything in it
    $statement = $connection->prepare("delete from {$database->getTableConstantes()}");
    $statement->execute();
    // prepare the insert
    $taxAdminData = $this->taxAdminData;
    $sqlInsert = "insert into {$database->getTableConstantes()}"
      . " ({$database->getColPlafondQfDemiPart()},"
      . " {$database->getColPlafondRevenusCelibatairePourReduction()},"
      . " {$database->getColPlafondRevenusCouplePourReduction()},"
      . " {$database->getColValueReducedHalfShare()},"
      . " {$database->getColPlafondDecoteCelibataire()},"
      . " {$database->getColPlafondDecoteCouple()},"
      . " {$database->getColIncomeLimitSingleForDiscount()},"
      . " {$database->getColPlafondImpotCouplePourDecote()},"
      . " {$database->getColMaxTenPercentDeduction()},"
      . " {$database->getMinTenPercentDeduction()})"
      . " values ("
      . ":QF-Half-Share-Threshold,"
      . ":singleIncomeLimitForReduction,"
      . ":coupleIncomeLimitForReduction,"
      . ":half-share-reduction-value,"
      . ":singleDiscountLimit,"
      . ":coupleDiscountLimit,"
      . ":singleTaxCeilingForDiscount,"
      . ":coupleTaxCeilingForDiscount,"
      . ":maximumTenPercentDeduction,"
      . ":minimumTenPercentDeduction)";
    $statement = $connection->prepare($sqlInsert);
    // execute the prepared statement
    $statement->execute([
      "half-share-deduction-limit" => $taxAdminData->getHalfShareDeductionLimit(),
      "singleIncomeLimitForReduction" => $taxAdminData->getSingleIncomeLimitForReduction(),
      "coupleIncomeLimitForReduction" => $taxAdminData->getCoupleIncomeLimitForReduction(),
      "half-share-reduction-value" => $taxAdminData->getHalfShareReductionValue(),
      "singleDiscountLimit" => $taxAdminData->getSingleDiscountLimit(),
      "coupleDiscountLimit" => $taxAdminData->getCoupleDiscountLimit(),
      "taxThresholdSingleForDeduction" => $taxAdminData->getPlafondImpotCelibatairePourDecote(),
      "taxLimitCoupleForDeduction" => $taxAdminData->getTaxLimitCoupleForDeduction(),
      "MaxTenPercentDeduction" => $taxAdminData->getMaxTenPercentDeduction(),
      "minimumTenPercentDeduction" => $taxAdminData->getMinimumTenPercentDeduction()
    ]);
  }

Comments

• line 1: the [fillTableConstantes] method receives an open connection as a parameter. We also know that a transaction has started within this connection;
• lines 5-6: the [tbconstantes] table is cleared;
• lines 9–31: preparation of the SQL insert statement. It is complex because there are 10 columns to initialize in this insert operation and the column names must be retrieved from the [$database] attribute;
• lines 33–44: execution of the insert statement. There is only one row to insert. Here again, the code is made complex by the need to retrieve the values to be inserted from the [$taxAdminData] attribute;

13.3.4. The main script

The main script relies on the [dao] layer to perform the data transfer:

<?php

// Strict adherence to the declared types of function parameters
declare (strict_types=1);

// namespace
namespace Application;

// PHP error handling
// ini_set("display_errors", "0");
// Include interface and classes
require_once __DIR__ . "/../Entities/BaseEntity.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/TaxPayerData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Utilities/Utilitaires.php";
require_once __DIR__ . "/../Dao/InterfaceDao.php";
require_once __DIR__ . "/../Dao/TraitDao.php";
require_once __DIR__ . "/../Dao/InterfaceDao4TransferAdminData2Database.php";
require_once __DIR__ . "/../Dao/DaoTransferAdminDataFromJsonFile2Database.php";
//
// definition of constants
const DATABASE_CONFIG_FILENAME = "../Data/database.json";
const TAXADMINDATA_FILENAME = "../Data/taxadmindata.json";

//
try {
  // creation of the [dao] layer
  $dao = new DaoTransferAdminDataFromJsonFile2Database(DATABASE_CONFIG_FILENAME, TAXADMINDATA_FILENAME);
  // Transfer data to the database
  $dao->transferAdminData2Database();
} catch (TaxException $ex) {
  // display the error
  print "The following error occurred: " . utf8_encode($ex->getMessage()) . "\n";
}
// end
print "Done\n";
exit;

Comments

• lines 12–21: loading the application’s classes and interfaces;
• lines 24-24: the two JSON files;
• line 30: instantiating the [DAO] layer by passing the two JSON files to the constructor;
• line 32: data transfer is performed;

When we run this code, we get the following result in the database:

Column [3] shows the values assigned by MySQL to the primary key [id]. Numbering starts at 1. The screenshot above was taken after running the script several times.

13.4. Tax calculation

13.4.1. Architecture

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

The [dao] layer implements an interface [InterfaceDao]. We built a class implementing this interface:

• [DaoImpotsWithTaxAdminDataInJsonFile] that retrieved tax data from a JSON file. That was version 04;

We will implement the [InterfaceDao] interface using a new class [DaoImpotsWithTaxAdminDataInDatabase] that will retrieve tax administration data from a MySQL database. The [dao] layer, as before, will write 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 is used to encapsulate the data from the following JSON file [taxpayersdata.json] into a class:

[
    {
        "married": "yes",
        "children": 2,
        "salary": 55555
    },
    {
        "married": "yes",
        "children": "2",
        "salary": "55555x"
    },
    {
        "married": "yes",
        "children": "2",
        "salary": 50000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50000
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 5,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 30,000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 20,000
    }
]

The [TaxPayerData] class is as follows:

<?php

// namespace
namespace Application;

// the data class
class TaxPayerData extends BaseEntity {
  // data required to calculate the taxpayer's tax
  protected $married;
  protected $children;
  protected $salary;
  // results of the tax calculation
  protected $tax;
  protected $surcharge;
  protected $discount;
  protected $reduction;
  protected $rate;

  // getters and setters
  …
}

Comments

• line 7: the [TaxPayerData] class extends the [BaseEntity] class. Since the methods of its parent class are sufficient, the [TaxPayerData] class does not define any of its own. Note that the attributes of the [TaxPayerData] class are identical to those in the JSON file [taxpayersdata.json];

13.4.3. The [dao] layer

13.4.3.1. The [TraitDao] trait

The [TraitDao] trait implements part of the [InterfaceDao] interface. Let’s review it:

<?php

// namespace
namespace Application;

interface InterfaceDao {

  // Read taxpayer data
  public function getTaxPayersData(string $taxPayersFilename, string $errorsFilename): array;

  // Read tax administration data (tax brackets)
  public function getTaxAdminData(): TaxAdminData;

  // saving results
  public function saveResults(string $resultsFilename, array $taxPayersData): void;
}

The [TraitDao] trait implements the [getTaxPayersData] and [saveResults] methods of the [InterfaceDao] interface. Since the definition of the [TaxPayerData] entity was changed between versions 04 and 05, we need to update the code in [TraitDao]:

<?php

// namespace
namespace Application;

trait TraitDao {

  // reading taxpayer data
  public function getTaxPayersData(string $taxPayersFilename, string $errorsFilename): array {
    // retrieve taxpayer data into an array
    $baseEntity = new BaseEntity();
    $baseEntity->setFromJsonFile($taxPayersFilename);
    $arrayOfAttributes = $baseEntity->getArrayOfAttributes();
    // array of taxpayer data
    $taxPayersData = [];
    // array of errors
    $errors = [];
    // loop through the array of attributes for elements of type [TaxPayerData]
    $i = 0;
    foreach ($arrayOfAttributes as $attributesOfTaxPayerData) {
      // validation
      $error = $this->check($attributesOfTaxPayerData);
      if (!$error) {
        // one more taxpayer
        $taxPayersData[] = (new TaxPayerData())->setFromArrayOfAttributes($attributesOfTaxPayerData);
      } else {
        // an error occurred - note the index of the invalid data
        $error = ["number" => $i] + $error;
        $errors[] = $error;
      }
      // next
      $i++;
    }
    // save the errors to a JSON file
    $string = "";
    foreach ($errors as $error) {
      $string .= \json_encode($error, JSON_UNESCAPED_UNICODE) . "\n";
    }
    $this->saveString($errorsFilename, $string);
    // result of the function
    return $taxPayersData;
  }

  private function check(array $attributesOfTaxPayerData): array {
    // check the data in [$taxPayerData]
    // list of incorrect attributes
    $attributes = [];
    // marital status must be yes or no
    $married = trim(strtolower($attributesOfTaxPayerData["married"]));
    $error = ($married !== "yes" and $married !== "no");
    if ($error) {
      // log the error
      $attributes[] = ["married" => $married];
    }
    // The number of children must be a positive integer or zero
    $children = trim($attributesOfTaxPayerData["children"]);
    if (!preg_match("/^\d+$/", $children)) {
      // log the error
      $error = TRUE;
      $attributes[] = ["children" => $children];
    } else {
      $children = (int) $children;
    }

    // The salary must be a positive integer or zero (without euro cents)
    $salary = trim($attributesOfTaxPayerData["salary"]);
    if (!preg_match("/^\d+$/", $salary)) {
      // log the error
      $error = TRUE;
      $attributes[] = ["salary" => $salary];
    } else {
      $salary = (int) $salary;
    }

    // error?
    if ($error) {
      // return with error
      return ["errors" => $attributes];
    } else {
      // return without error
      return [];
    }
  }

  // Saving results
  public function saveResults(string $resultsFilename, array $taxPayersData): void {
    // Save the array [$taxPayersData] to the text file [$resultsFileName]
    // if the text file [$resultsFileName] does not exist, it is created
    // Construct the JSON string of the results
    $string = "[" . implode(",
", $taxPayersData) . "]";
    // saving this string
    $this->saveString($resultsFilename, $string);
  }

  // Save the results of an array to a text file
  private function saveString(string $fileName, string $data): void {
    // Save the string [$data] to the text file [$fileName]
    // if the text file [$fileName] does not exist, it is created
    if (file_put_contents($fileName, $data) === FALSE) {
      throw new ExceptionImpots("Error saving data to the text file [$fileName]");
    }
  }

}

Comments

• [TraitDao] implements the methods [getTaxPayersData] (line 9) and [saveResults] (line 86) of the [InterfaceDao] interface;
• line 9: 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 errors [errors.json];
• lines 11–13: the contents of the JSON file containing taxpayer data are transferred to an associative array [$arrayOfAttributes]. If the JSON file proves unusable, an [ExceptionImpots] exception is thrown;
• line 15: the array [$taxPayersData] will contain taxpayer data encapsulated in objects of type [TaxPayerData];
• line 17: errors are accumulated in the array [$errors];
• lines 99–33: construction of the array [$taxPayersData];
• line 22: Before being encapsulated in a [TaxPayerData] type, the data is verified. The [check] method returns:
  • an array [‘errors’=>[…]] containing the erroneous attributes if the data is incorrect;
  • an empty array if the data is correct;
• Line 25: When the data is valid. A new [TaxPayerData] object is created and added to the [$taxPayersData] array;
• lines 26–30: case where the data is invalid. The error record includes the ID of the incorrect [TaxPayerData] object in the JSON file so the user can locate it, then the error is added to the [$errors] array;
• lines 35–39: the errors encountered are logged to the JSON file [$errorsFilename] passed as a parameter in line 9;
• line 41: the array of constructed [TaxPayerData] objects is returned: this was the method’s objective;
• lines 44–83: the private method [check] verifies the validity of the parameters [married, children, salary] of the array [$attributesOfTaxPayerData] passed as a parameter on line 44. If there are any invalid attributes, it collects them in the array [$attributes] (lines 47, 53, 60, 70) in the form of an array [‘invalid attribute’=> value of the invalid attribute];
• line 78: if there are errors, return an array [‘errors’=>$attributes];
• line 81: if there are no errors, an empty array of errors is returned;
• lines 86–93: implementation of the [saveResults] method of the [InterfaceDao] interface;
• line 90: we construct the JSON string to be saved in the JSON file [$resultsFilename] passed as a parameter in line 86. We must construct the JSON string from an array:
  • each element of the array is separated from the next by a comma and a newline;
  • the entire array is enclosed in square brackets [];
• line 92: the JSON string is saved to the JSON file [$resultsFilename];

13.4.3.2. The [DaoImpotsWithTaxAdminDataInDatabase] class

The class [DaoImpotsWithTaxAdminDataInDatabase] implements the [InterfaceDao] interface as follows:

<?php

// namespace
namespace Application;

// definition of a class ImpotsWithDataInDatabase
class DaoImpotsWithTaxAdminDataInDatabase implements InterfaceDao {
  // use of a trait
  use TraitDao;
  // the TaxAdminData object containing tax bracket data
  private $taxAdminData;
  // the object of type [Database] containing the database properties
  private $database;

  // constructor
  public function __construct(string $databaseFilename) {
    // store the database's JSON configuration
    $this->database = (new Database())->setFromJsonFile($databaseFilename);
    // prepare the attribute
    $this->taxAdminData = new TaxAdminData();
    try {
      // Open the database connection
      $connection = new \PDO(
        $this->database->getDsn(),
        $this->database->getId(),
        $this->database->getPwd());
      // We want an exception to be thrown for every DBMS error
      $connection->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
      // start a transaction
      $connection->beginTransaction();
      // populate the tax brackets table
      $this->getTranches($connection);
      // populate the constants table
      $this->getConstants($connection);
      // commit the transaction
      $connection->commit();
    } catch (\PDOException $ex) {
      // Is there a transaction in progress?
      if (isset($connection) && $connection->inTransaction()) {
        // roll back the transaction
        $connection->rollBack();
      }
      // throw the exception to the calling code
      throw new ExceptionImpots($ex->getMessage());
    } finally {
      // close the connection
      $connection = NULL;
    }
  }

  // read data from the database
  private function getSlices($connection): void {
    …
  }

  // read the constants table
  private function getConstants($connection): void {
    …
  }

  // returns the data needed to calculate the tax
  public function getTaxAdminData(): TaxAdminData {
    return $this->taxAdminData;
  }

}

Comments

• line 4: we keep the namespace already used for the other implementations of the [dao] layer;
• line 7: the [DaoImpotsWithTaxAdminDataInDatabase] class implements the [InterfaceDao] interface;
• line 9: we import the trait [TraitDao]. We know that this trait implements part of the interface. The only method left to implement is the [getTaxAdminData] method in lines 62–64. This method simply returns the private attribute [taxAdminData] from line 11. We can infer that the constructor must initialize this attribute. That is its sole role;
• Line 16: The constructor receives a single parameter, [$databaseFilename], which is the name of the JSON file [database.json] that defines the MySQL database [dbimpots-2019];
• line 18: the JSON file [$databaseFilename] is used to create a [Database] object, which is constructed and stored in the [$database] attribute from line 13. If the JSON file could not be processed correctly, an [ExceptionImpots] exception is thrown;
• line 20: the object [$this→taxAdminData] is created, which the constructor must initialize;
• lines 22–26: the database connection is opened. Note the notation [\PDO] to refer to the PHP [PDO] class. Since we are in the [Application] namespace, if we simply wrote [PDO], this relative name would be prefixed with the current namespace, resulting in the class [Application\PDO], which does not exist;
• line 28: if an error occurs, the DBMS will throw a \PDOException (line 37);
• line 30: we start a transaction. This isn’t really necessary since only two SQL statements will be executed, and these statements do not modify the database. We do this, however, to isolate ourselves from other database users;
• line 32: the tax bracket table [tbtranches] is read using the private method [getTranches] from line 52;
• line 34: the calculation constants table [tbconstantes] is read using the private method [getConstantes] from line 57;
• line 36: if we reach this line, it means everything went well. We therefore commit the transaction;
• lines 37–42: if we reach this point, it means an exception has occurred. We therefore roll back the transaction if one was in progress (lines 39–42). Line 44: to ensure consistent exceptions, we re-throw the received exception message, this time as an exception of type [ExceptionImpots];
• lines 45–48: In all cases (whether an exception occurred or not), we close the connection;

The [getTranches] method is as follows:

private function getTranches($connection): void {
    // shortcuts
    $database = $this->database;
    $taxAdminData = $this->taxAdminData;
    // prepare the SELECT query
    $statement = $connection->prepare(
      "select {$database->getColLimites()}," .
      " {$database->getColLimites()}," .
      " {$database->getColCoeffN()}" .
      " from {$database->getTableTranches()}");
    // execute the prepared statement with the tax bracket values
    $statement->execute();
    // process the result
    $limits = [];
    $coeffR = [];
    $coeffN = [];
    // populate the three arrays
    while ($row = $statement->fetch(\PDO::FETCH_OBJ)) {
      $limits[] = (float) $row->{$database->getColLimits()};
      $coeffR[] = (float) $row->{$database->getColCoeffR()};
      $coeffN[] = (float) $row->{$database->getColCoeffN()};
    }
    // store the data in the [$taxAdminData] attribute of the class
    $taxAdminData->setFromArrayOfAttributes([
      "limits" => $limits,
      "coeffR" => $coeffR,
      "coeffN" => $coeffN
    ]);
  }

Comments

• line 1: the method receives [$connexion] as a parameter, which is an open connection with a transaction in progress;
• lines 2–4: two shortcuts are created to avoid having to write [$this->database] and [$taxAdminData = $this->taxAdminData] throughout the code. These are copies of object references, not copies of the objects themselves;
• lines 6–10: the SELECT statement is prepared, then executed on line 12;
• lines 13–22: the result of the SELECT is processed. The received information is stored in three arrays [limits, coeffR, coeffN];
• lines 24–28: the three arrays are used to initialize the class’s [$this->taxAdminData] attribute;

The private method [getConstantes] is as follows:

private function getConstants($connection): void {
    // shortcuts
    $database = $this->database;
    $taxAdminData = $this->taxAdminData;
    // prepare the SELECT query
    $select = "select {$database->getColPlafondQfDemiPart()}," .
      "{$database->getColPlafondRevenusCelibatairePourReduction()}," .
      "{$database->getColPlafondRevenusCouplePourReduction()}," . "{$database->getColValeurReducDemiPart()}," .
      "{$database->getColPlafondDecoteCelibataire()}," . "{$database->getColPlafondDecoteCouple()}," .
      "{$database->getColIncomeCeilingSingleForDiscount()}," . "{$database->getColIncomeCeilingCoupleForDiscount()}," .
      "{$database->getColMaxTenPercentDeduction()}," . "{$database->getColMinTenPercentDeduction()}" .
      " from {$database->getTableConstants()}";
    $statement = $connection->prepare($select);
    // execute the prepared statement
    $statement->execute();
    // process the result - only 1 row here
    $row = $statement->fetch(\PDO::FETCH_OBJ);
    // initialize the [$taxAdminData] attribute
    $taxAdminData->setPlafondQfDemiPart($row->{$database->getColPlafondQfDemiPart()});
    $taxAdminData->setSingleIncomeThresholdForReduction(
      $row->{$database->getColPlafondRevenusCelibatairePourReduction()});
    $taxAdminData->setIncomeLimitCoupleForReduction($row->{$database->getColIncomeLimitCoupleForReduction()});
    $taxAdminData->setHalf-Share-Reduction-Value($row->{$database->getColHalf-Share-Reduction-Value()});
    $taxAdminData->setSingleTaxDeductionLimit($row->{$database->getColSingleTaxDeductionLimit()});
    $taxAdminData->setCoupleDeductionLimit($row->{$database->getColCoupleDeductionLimit()});
    $taxAdminData->setSingleTaxLimitForDeduction($row->{$database->getColSingleTaxLimitForDeduction()});
    $taxAdminData->setCoupleTaxDeductionLimit($row->{$database->getColPlafondImpotCouplePourDecote()});
    $taxAdminData->setMaxTenPercentDeduction($row->{$database->getColMaxTenPercentDeduction()});
    $taxAdminData->setMinTenPercentDeduction($row->{$database->getColMinTenPercentDeduction()});
  }

Comments

• line 1: the method receives [$connection] as a parameter, which is an open connection with an ongoing transaction;
• lines 2–4: two shortcuts are created to avoid having to write [$this->database] and [$taxAdminData = $this->taxAdminData] throughout the code. These are copies of object references, not copies of the objects themselves;
• lines 6–15: the SELECT statement is prepared, then executed on line 15;
• lines 17–29: the result of the SELECT is processed. The retrieved information is used to initialize the [$this->taxAdminData] attribute of the class;

Note: Note that the class does not depend on the MySQL DBMS. It is the calling code that specifies the DBMS used via the database DSN.

13.4.4. The [business] layer

• We have just implemented the [DAO] layer (3);
• Since we have adhered to the [InterfaceDao] interface, the [business] layer (2) can theoretically remain unchanged. However, we have not only modified the [DAO] layer. We have also modified the entities, which are shared by all layers;

The [business] layer implements the following [BusinessInterface] interface:

<?php

// namespace
namespace Application;

interface BusinessInterface {

  // Calculate a taxpayer's taxes
  public function calculateTax(string $married, int $children, int $salary): array;

  // Calculate taxes in batch mode
  public function executeBatchTaxes(string $taxPayersFileName, string $resultsFileName, string $errorsFileName): void;
}

• line 12: the [executeBatchImpots] method now uses the JSON file [$taxPayersFileName], whereas in version 04, it was a basic text file. ;

In version 04, the [executeBatchImpots] method was as follows:

public function executeBatchImpots(string $taxPayersFileName, string $resultsFileName, string $errorsFileName): void {
    // let exceptions from the [dao] layer propagate
    // retrieve taxpayer data
    $taxPayersData = $this->dao->getTaxPayersData($taxPayersFileName, $errorsFileName);
    // results array
    $results = [];
    // process them
    foreach ($taxPayersData as $taxPayerData) {
      // Calculate the tax
      $result = $this->calculateTax(
        $taxPayerData->getMarried(),
        $taxPayerData->getChildren(),
        $taxPayerData->getSalary());
      // update [$taxPayerData]
      $taxPayerData->setAmount($result["tax"]);
      $taxPayerData->setDiscount($result["discount"]);
      $taxPayerData->setSurcharge($result["surcharge"]);
      $taxPayerData->setRate($result["rate"]);
      $taxPayerData->setReduction($result["reduction"]);
      // add the result to the results array
      $results[] = $taxPayerData;
    }
    // save the results
    $this->dao->saveResults($resultsFileName, $results);
  }

• Line 15 is now incorrect. In the new definition of the [TaxPayerData] class, the [setMontant] method no longer exists;

In version 05, the [executeBatchImpots] method will be as follows:

public function executeBatchImpots(string $taxPayersFileName, string $resultsFileName, string $errorsFileName): void {
    // we let exceptions from the [dao] layer propagate
    // retrieve taxpayer data
    $taxPayersData = $this->dao->getTaxPayersData($taxPayersFileName, $errorsFileName);
    // results array
    $results = [];
    // process them
    foreach ($taxPayersData as $taxPayerData) {
      // calculate the tax
      $result = $this->calculateTax(
        $taxPayerData->isMarried(),
        $taxPayerData->getChildren(),
        $taxPayerData->getSalary());
      // populate [$taxPayerData]
      $taxPayerData->setFromArrayOfAttributes($result);
      // put the result into the results array
      $results[] = $taxPayerData;
    }
    // save the results
    $this->dao->saveResults($resultsFileName, $results);
  }

Comments

• line 15: instead of using the individual setters of the [TaxPayerData] class, we use its global setter [setFromArrayOfAttributes];
• the rest of the code does not need to be modified;

13.4.5. The main script

• We have just implemented the [DAO] (3) and [business logic] (2) layers;
• we still need to write the main script (1);

The main script is similar to that of version 04:

<?php

// Strict adherence to the declared types of function parameters
declare (strict_types=1);

// namespace
namespace Application;

// PHP error handling
//ini_set("display_errors", "0");
// include interface and classes
require_once __DIR__ . "/../Entities/BaseEntity.php";
require_once __DIR__ . "/../Entities/TaxAdminData.php";
require_once __DIR__ . "/../Entities/TaxPayerData.php";
require_once __DIR__ . "/../Entities/Database.php";
require_once __DIR__ . "/../Entities/TaxException.php";
require_once __DIR__ . "/../Utilities/Utilitaires.php";
require_once __DIR__ . "/../Dao/InterfaceDao.php";
require_once __DIR__ . "/../Dao/TraitDao.php";
require_once __DIR__ . "/../Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
require_once __DIR__ . "/../Business/BusinessInterface.php";
require_once __DIR__ . "/../Business/Business.php";
//
// definition of constants
const DATABASE_CONFIG_FILENAME = "../Data/database.json";
const TAXADMINDATA_FILENAME = "../Data/taxadmindata.json";
const RESULTS_FILENAME = "../Data/results.json";
const ERRORS_FILENAME = "../Data/errors.json";
const TAXPAYERSDATA_FILENAME = "../Data/taxpayersdata.json";

try {
  // Create the [dao] layer
  $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
  // Create the [business logic] layer
  $business = new Business($dao);
  // Calculate taxes in batch mode
  $businessLogic = new BusinessLogic($dao);
} catch (TaxException $ex) {
  // display the error
  print "An error occurred: " . utf8_encode($ex->getMessage()) . "\n";
}
// end
print "Done\n";
exit;

Comments

• lines 12–22: loading all files from version 05;
• lines 25–29: the names of the application’s various JSON files;
• line 33: construction of the [DAO] layer;
• line 35: construction of the [business] layer;
• line 37: calling the [executeBatchImpots] method of the [business] layer;

Results

The application generates two JSON files:

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

The [errors.json] file is as follows:

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

This means that in [taxpayersdata.json], the first entry in the taxpayers table is incorrect. The [taxpayersdata.json] file was as follows:

[
    {
        "married": "yes",
        "children": 2,
        "salary": 55555
    },
    {
        "married": "yes",
        "children": "2x",
        "salary": "55555x"
    },
    {
        "married": "yes",
        "children": "2",
        "salary": 50000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50000
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 5,
        "salary": 100,000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 100,000
    },
    {
        "married": "yes",
        "children": 2,
        "salary": 30,000
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200,000
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 20,000
    }
]

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,
        "discount": 347,
        "rate": 0.14
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 50,000,
        "tax": 0,
        "surcharge": 0,
        "discount": 720,
        "reduction": 0,
        "rate": 0.14
    },
    {
        "married": "no",
        "children": 2,
        "salary": 100000,
        "tax": 19,884,
        "surcharge": 4,480,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    },
    {
        "married": "no",
        "children": 3,
        "salary": 100000,
        "tax": 16,782,
        "surcharge": 7176,
        "discount": 0,
        "reduction": 0,
        "rate": 0.41
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 100,000,
        "tax": 9,200,
        "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
    },
    {
        "married": "no",
        "children": 0,
        "salary": 200000,
        "tax": 64,210,
        "surcharge": 7,498,
        "discount": 0,
        "reduction": 0,
        "rate": 0.45
    },
    {
        "married": "yes",
        "children": 3,
        "salary": 20000,
        "tax": 0,
        "surcharge": 0,
        "discount": 0,
        "reduction": 0,
        "rate": 0
    }
]

These results are consistent with those of version 04.

13.5. [Codeception] Tests

As was done in the section linked for version 04, we will write [Codeception] tests for version 05.

13.5.1. Testing the [dao] layer

The [DaoTest.php] test is as follows:

<?php

// Strict adherence to the declared types of function parameters
declare (strict_types=1);

// namespace
namespace Application;

// root directories
define("ROOT", "C:/Data/st-2019/dev/php7/poly/scripts-console/impots/version-05");
define("VENDOR", "C:/myprograms/laragon-lite/www/vendor");

// include interface and classes
require_once ROOT . "/Entities/BaseEntity.php";
require_once ROOT . "/Entities/TaxAdminData.php";
require_once ROOT . "/Entities/TaxPayerData.php";
require_once ROOT . "/Entities/Database.php";
require_once ROOT . "/Entities/TaxException.php";
require_once ROOT . "/Utilities/Utilities.php";
require_once ROOT . "/Dao/InterfaceDao.php";
require_once ROOT . "/Dao/DaoProcess.php";
require_once ROOT . "/Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
require_once ROOT . "/Business/BusinessInterface.php";
require_once ROOT . "/Business/Business.php";
// third-party libraries
require_once VENDOR . "/autoload.php";

// definition of constants
const DATABASE_CONFIG_FILENAME = ROOT ."/Data/database.json";
const TAXADMINDATA_FILENAME = ROOT ."/Data/taxadmindata.json";
const RESULTS_FILENAME = ROOT ."/Data/results.json";
const ERRORS_FILENAME = ROOT ."/Data/errors.json";
const TAXPAYERSDATA_FILENAME = ROOT ."/Data/taxpayersdata.json";

class DaoTest extends \Codeception\Test\Unit {
  // TaxAdminData
  private $taxAdminData;

  public function __construct() {
    parent::__construct();
    // Create the [dao] layer
    $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
    $this->taxAdminData = $dao->getTaxAdminData();
  }

  // tests
  public function testTaxAdminData() {
    // calculation constants
    $this->assertEquals(1551, $this->taxAdminData->getPlafondQfDemiPart());
    …
  }

}

Comments

• lines 9–33: definition of the test environment. We use the same one as the main script [MainCalculateImpotsWithTaxAdminDataInMySQLDatabase] described in the linked section;
• lines 39–44: construction of the [dao] layer;
• line 43: the attribute [$this→taxAdminData] contains the data to be tested;
• lines 47–51: the [testTaxAdminData] method is the one described in the linked section;

The test results are as follows:

13.5.2. Testing the [business] layer

The [MetierTest.php] test is as follows:

<?php

// Strict adherence to the declared types of function parameters
declare (strict_types=1);

// namespace
namespace Application;

// root directories
define("ROOT", "C:/Data/st-2019/dev/php7/poly/scripts-console/impots/version-05");
define("VENDOR", "C:/myprograms/laragon-lite/www/vendor");

// Include interfaces and classes
require_once ROOT . "/Entities/BaseEntity.php";
require_once ROOT . "/Entities/TaxAdminData.php";
require_once ROOT . "/Entities/TaxPayerData.php";
require_once ROOT . "/Entities/Database.php";
require_once ROOT . "/Entities/TaxException.php";
require_once ROOT . "/Utilities/Utilities.php";
require_once ROOT . "/Dao/InterfaceDao.php";
require_once ROOT . "/Dao/DaoProcess.php";
require_once ROOT . "/Dao/DaoImpotsWithTaxAdminDataInDatabase.php";
require_once ROOT . "/Business/BusinessInterface.php";
require_once ROOT . "/Business/Business.php";
// third-party libraries
require_once VENDOR . "/autoload.php";

// definition of constants
const DATABASE_CONFIG_FILENAME = ROOT ."/Data/database.json";
const TAXADMINDATA_FILENAME = ROOT ."/Data/taxadmindata.json";
const RESULTS_FILENAME = ROOT ."/Data/results.json";
const ERRORS_FILENAME = ROOT ."/Data/errors.json";
const TAXPAYERSDATA_FILENAME = ROOT ."/Data/taxpayersdata.json";

class MetierTest extends \Codeception\Test\Unit {
  // business layer
  private $business;

  public function __construct() {
    parent::__construct();
    // creation of the [DAO] layer
    $dao = new DaoImpotsWithTaxAdminDataInDatabase(DATABASE_CONFIG_FILENAME);
    // creation of the [business] layer
    $this->business = new Business($dao);
  }

  // tests
  public function test1() {
    $result = $this->business->calculateTax("yes", 2, 55555);
    $this->assertEqualsWithDelta(2815, $result["tax"], 1);
    $this->assertEqualsWithDelta(0, $result["surcharge"], 1);
    $this->assertEqualsWithDelta(0, $result["discount"], 1);
    $this->assertEqualsWithDelta(0, $result["reduction"], 1);
    $this->assertEquals(0.14, $result["rate"]);
  }
…………………………………………………………………………………………………………………..
public function test11() {
    $result = $this->businessLogic->calculateTax("yes", 3, 200000);
    $this->assertEqualsWithDelta(42842, $result["tax"], 1);
    $this->assertEqualsWithDelta(17283, $result["surcharge"], 1);
    $this->assertEqualsWithDelta(0, $result["discount"], 1);
    $this->assertEqualsWithDelta(0, $result["reduction"], 1);
    $this->assertEquals(0.41, $result["rate"]);
  }

}

Comments

• lines 9–33: definition of the test environment. We use the same one as the main script [MainCalculateImpotsWithTaxAdminDataInMySQLDatabase] described in the linked section;
• lines 39–45: construction of the [dao] and [business] layers;
• line 44: the attribute [$this→business] references the [business] layer;
• lines 47–64: the methods [test1, test2…, test11] are those described in the linked section;

The test results are as follows: