Skip to content

8. Вправа IMPOTS з MySQL

Ми вже написали три версії цього завдання. В останній версії використовувався клас для розрахунку податку. Цей клас отримував дані, необхідні для розрахунку, з текстового файлу. Тепер він буде отримувати їх із бази даних. Для цього ми пишемо перший код, який перенесе дані з текстового файлу в базу даних.

Текстовий файл impots.txt має такий вигляд:

12620:13190:15640:24740:31810:39970:48360:55790:92970:127860:151250:172040:195000:0
0:0.05:0.1:0.15:0.2:0.25:0.3:0.35:0.4:0.45:0.5:0.55:0.6:0.65
0:631:1290.5:272.5:3309.5:4900:6898.5:9316.5:12106:16754.5:23147.5:30710:39312:49062

База даних, яку потрібно створити, має такий вигляд:

База має назву [dbimpots] і містить єдину таблицю [impots]. Доступ до неї здійснює користувач [root] без пароля.

8.1. Перенесення текстового файлу в таблицю MySQL (txt2mysql)


<?php

// передає текстовий файл з даними, необхідними для розрахунку податків
// у таблицю MySQL
// дані
$IMPOTS = "impots.txt";
$BASE = "dbimpots";
$TABLE = "impots";
$USER = "root";
$PWD = "";
$HOTE = "localhost";

// дані, необхідні для розрахунку податку, були розміщені у файлі $IMPOTS
// по одному рядку на таблицю у вигляді
// val1:val2:val3,...
list($erreur, $limites, $coeffR, $coeffN) = getTables($IMPOTS);
// Чи сталася помилка?
if ($erreur) {
  print "$erreur\n";
  exit;
}//if
// ці масиви переносимо в таблицю MySQL
$erreur = copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE);
if ($erreur)
  print "$erreur\n";
else
  print "Transfert opéré\n";
// кінець
exit;

// --------------------------------------------------------------------------
function copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE) {
  // копіюємо 3 числові масиви $limites, $coeffR, $coeffN
  // у таблицю $TABLE бази даних MySQL $BASE
  // база даних MySQL знаходиться на комп'ютері $HOTE
  // користувач ідентифікується за допомогою $USER та $PWD
  // вхід
  list($erreur, $connexion) = connecte("mysql:host=$HOTE;dbname=$BASE", $USER, $PWD);
  if ($erreur)
    return "Erreur lors de la connexion à MySql sous l'identité ($HOTE,$USER,$PWD) : $erreur\n";
  // видалення таблиці
  $requête = "drop table $TABLE";
  exécuteRequête($connexion, $requête);
  // створення таблиці
  $requête = "create table $TABLE (limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))";
  list($erreur, $res) = exécuteRequête($connexion, $requête);
  if ($erreur)
    return "$requête : erreur ($erreur)";
  // заповнення
  for ($i = 0; $i < count($limites); $i++) {
    // запит на вставку
    $requête = "insert into $TABLE (limites,coeffR,coeffN) values ($limites[$i],$coeffR[$i],$coeffN[$i])";
    // виконання запиту
    list($erreur, $res) = exécuteRequête($connexion, $requête);
    // повернення у разі помилки
    if ($erreur)
      return "$requête : erreur ($erreur)";
  }//цикл
  // все закінчено — виходимо з системи
  déconnecte($connexion);
  // повернення без помилки
  return "";
}
// --------------------------------------------------------------------------
function getTables($IMPOTS) {
  // $IMPOTS: ім'я файлу, що містить дані з таблиць $limites, $coeffR, $coeffN
  ...
  // кінець
  return array("", $limites, $coeffR, $coeffN);
}
// --------------------------------------------------------------------------
function cutNewLinechar($ligne) {
  // видаляється маркер кінця рядка з $ligne, якщо він існує
  ...
  // кінець
  return($ligne);
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // підключає ($login,$pwd) до бази $dsn
  // повертає ідентифікатор з’єднання та повідомлення про помилку
  ...
}

//встановлює з'єднання
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // закриває з’єднання, ідентифіковане як $connexion
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // виконує запит $sql через з’єднання $connexion
  // повертає масив із 2 елементів ($erreur, $résultat)
 ...
}

Результати на екрані:

Transfert opéré

8.2. Програма для розрахунку податку ( impots_04)

Ця версія використовує клас Impôts, який отримує з бази даних значення, необхідні для розрахунку податку. Тут ми вводимо нове поняття — підготовлений запит. Виконання команди SQL за допомогою SGBD відбувається у два етапи:

  1. запит готується: SGBD готує оптимізований план виконання для запиту. Мета полягає в тому, щоб виконати його якомога ефективніше.
  1. запит виконується.

Якщо один і той самий запит виконується N разів, обидва попередні етапи виконуються N разів. Однак можна підготувати запит один раз і виконати його N разів. Для цього потрібно використовувати підготовлені запити. Якщо $requête — це команда SQL, яку потрібно виконати, а $connexion — об’єкт PDO, що представляє з’єднання:

  • $statement=$connexion->prepare($requête) готує запит і повертає «підготовлений» запит
  • $statement->execute() виконує підготовлений запит.

Якщо підготовлений запит є командою select, то

  • $statement->fetchAll() повертає всі рядки таблиці, отриманої в результаті виконання select, у вигляді масиву T, де T[i,j] є значенням стовпця j рядка i таблиці
  • $statement->fetch() повертає поточний рядок таблиці у вигляді масиву T, де T[j] — це значення стовпця j у рядку

Підготовлений запит має й інші переваги, окрім більшої ефективності. Зокрема, він забезпечує вищий рівень безпеки. Тому його слід використовувати систематично.


<?php

// тест -----------------------------------------------------
// визначення констант
$DATA = "data.txt";
$RESULTATS = "resultats.txt";
$TABLE = "impots";
$BASE = "dbimpots";
$USER = "root";
$PWD = "";
$HOTE="localhost";

// дані, необхідні для розрахунку податку, були розміщені в таблиці mysqL $TABLE
//, що належить до бази даних $BASE. Таблиця має таку структуру
// межі decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
// параметри платників податків (сімейний стан, кількість дітей, річний заробіток)
// були розміщені у текстовому файлі $DATA по одному рядку на кожного платника податків
// результати (сімейний стан, кількість дітей, річний дохід, сума податку до сплати) розміщено у
// текстовий файл $RESULTATS, по одному результату на рядок

// створюється об’єкт «Податки»
$I = new Impôts($HOTE, $USER, $PWD, $BASE, $TABLE);
// чи сталася помилка?
$erreur=$I->getErreur();
if ($erreur) {
  print "$I->erreur\n";
  exit;
}//if
// створюється об’єкт «Утиліти»
$u = new Utilitaires();

// відкриття файлу з даними платників податків
$data = fopen($DATA, "r");
if (!$data) {
  print "Impossible d'ouvrir en lecture le fichier des données [$DATA]\n";
  exit;
}

// відкриття файлу результатів
$résultats = fopen($RESULTATS, "w");
if (!$résultats) {
  print "Impossible de créer le fichier des résultats [$RESULTATS]\n";
  exit;
}

// обробляється поточний рядок файлу даних платників податків
while ($ligne = fgets($data, 100)) {
  // видаляємо можливий маркер кінця рядка
  $ligne = $u->cutNewLineChar($ligne);
  // витягуються 3 поля «одружений», «діти», «зарплата», що утворюють $ligne
  list($marié, $enfants, $salaire) = explode(",", $ligne);
  // розраховується податок
  $impôt = $I->calculer($marié, $enfants, $salaire);
  // записуємо результат
  fputs($résultats, "$marié:$enfants:$salaire:$impôt\n");
  // наступні дані
}
// закриваємо файли
fclose($data);
fclose($résultats);

// кінець
exit;

// ---------------------------------------------------------------------------------
// визначення класу «Податки»
class Impôts {

  // атрибути: 3 таблиці даних
  private $limites;
  private $coeffR;
  private $coeffN;
  private $erreur;
  private $nbLimites;

  // геттер
  public function getErreur(){
    return $this->erreur;
  }
  // конструктор

  function __construct($HOTE, $USER, $PWD, $BASE, $TABLE) {
    // ініціалізує атрибути $limites, $coeffR, $coeffN
    // дані, необхідні для розрахунку податку, були розміщені в таблиці MySQL $TABLE
    //, що належить до бази даних $BASE. Таблиця має таку структуру
    // limites decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
    // підключення до бази даних MySQL на машині $HOTE здійснюється під ідентифікатором ($USER,$PWD)
    // ініціалізує поле $erreur із можливою помилкою
    // очищається, якщо помилки немає
    // 
    // підключення до бази даних MySQL
    $DSN = "mysql:host=$HOTE;dbname=$BASE";
    list($erreur, $connexion) = connecte($DSN, $USER, $PWD);
    if ($erreur) {
      $this->erreur = "Erreur lors de la connexion à MySql sous l'identité ($HOTE,$USER,$PWD) : $erreur\n";
      return;
    }
    // читання таблиці $TABLE
    $requête = "select limites,coeffR,coeffN from $TABLE";
    // виконує запит $requête через з'єднання $connexion
    try {
      $statement = $connexion->prepare($requête);
      $statement->execute();
      // обробка результату запиту
      while ($colonnes = $statement->fetch()) {
        $this->limites[] = $colonnes[0];
        $this->coeffR[] = $colonnes[1];
        $this->coeffN[] = $colonnes[2];
      }
      // помилок немає
      $this->erreur = "";
      // кількість елементів масиву перевищує межі
      $this->nbLimites = count($this->limites);
    } catch (PDOException $e) {
      $this->erreur = $e->getMessage();
    }
     // відключення
    déconnecte($connexion);
  }
  // --------------------------------------------------------------------------
  function calculer($marié, $enfants, $salaire) {
    // $marié: так, ні
    // $enfants: кількість дітей
    // $salaire: річний оклад
    
  // чи об’єкт перебуває у належному стані?
    if ($this->erreur)
      return -1;

    // кількість часток
    ...
  }

}

// ---------------------------------------------------------------------------------
// клас службових функцій
class Utilitaires {

  function cutNewLinechar($ligne) {
    // видаляється маркер кінця рядка з $ligne, якщо він існує
    ...
  }

}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // підключає ($login, $pwd) до бази $dsn
  // повертає ідентифікатор з’єднання та повідомлення про помилку
  ...
}

//встановлює з'єднання
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // закриває з’єднання, ідентифіковане за $connexion
  ...
}

Результати: такі самі, як і в попередніх версіях.

Коментарі

Нові елементи містяться у рядках 98–109:

  • рядок 99: команда SQL select, яка дозволить отримати дані, необхідні для розрахунку податку.
  • рядок 102: підготовка запиту SQL select
  • рядок 103: виконання підготовленого запиту
  • рядки 105–109: построкова обробка таблиці результатів select
https://stahe.github.io
Цей навчальний курс, написаний Сержем Тахе, надається у відкритий доступ відповідно до умов Creative Commons «Вказання авторства — Некомерційне використання — Поширення на тих самих умовах 3.0 без змін.