Skip to content

8. Ćwiczenie IMPOTS z MySQL

Napisaliśmy już trzy wersje tego ćwiczenia. W ostatniej wersji wykorzystano klasę służącą do obliczania podatku. Klasa ta pobierała dane niezbędne do obliczeń z pliku tekstowego. Od teraz będzie je pobierać z bazy danych. W tym celu piszemy pierwszy kod, który przeniesie dane z pliku tekstowego do bazy danych.

Plik tekstowy impots.txt ma następującą treść:

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

Baza danych, którą należy utworzyć, wygląda następująco:

Baza nosi nazwę [dbimpots] i zawiera jedną tabelę o nazwie [impots]. Korzysta z niej użytkownik [root] bez hasła.

8.1. Przeniesienie pliku tekstowego do tabeli MySQL (txt2mysql)


<?php

// przesyła plik tekstowy zawierający dane niezbędne do obliczenia podatków
// do tabeli MySQL
// dane
$IMPOTS = "impots.txt";
$BASE = "dbimpots";
$TABLE = "impots";
$USER = "root";
$PWD = "";
$HOTE = "localhost";

// dane niezbędne do obliczenia podatku zostały umieszczone w pliku $IMPOTS
// w postaci jednego wiersza na tabelę w formacie
// val1:val2:val3,...
list($erreur, $limites, $coeffR, $coeffN) = getTables($IMPOTS);
// czy wystąpił błąd?
if ($erreur) {
  print "$erreur\n";
  exit;
}//if
// przenosimy te tabele do tabeli MySQL
$erreur = copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE);
if ($erreur)
  print "$erreur\n";
else
  print "Transfert opéré\n";
// koniec
exit;

// --------------------------------------------------------------------------
function copyToMysql($limites, $coeffR, $coeffN, $HOTE, $USER, $PWD, $BASE, $TABLE) {
  // kopiuje 3 tabele liczbowe $limites, $coeffR, $coeffN
  // do tabeli $TABLE w bazie danych MySQL $BASE
  // baza danych MySQL znajduje się na komputerze $HOTE
  // użytkownik jest identyfikowany przez $USER i $PWD
  // logowanie
  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";
  // usunięcie tabeli
  $requête = "drop table $TABLE";
  exécuteRequête($connexion, $requête);
  // utworzenie tabeli
  $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)";
  // wypełnianie
  for ($i = 0; $i < count($limites); $i++) {
    // zapytanie o wstawienie
    $requête = "insert into $TABLE (limites,coeffR,coeffN) values ($limites[$i],$coeffR[$i],$coeffN[$i])";
    // wykonanie zapytania
    list($erreur, $res) = exécuteRequête($connexion, $requête);
    // powrót w przypadku błędu
    if ($erreur)
      return "$requête : erreur ($erreur)";
  }//for
  // koniec – rozłączamy się
  déconnecte($connexion);
  // powrót bez błędu
  return "";
}
// --------------------------------------------------------------------------
function getTables($IMPOTS) {
  // $IMPOTS: nazwa pliku zawierającego dane z tabel $limites, $coeffR, $coeffN
  ...
  // koniec
  return array("", $limites, $coeffR, $coeffN);
}
// --------------------------------------------------------------------------
function cutNewLinechar($ligne) {
  // usuwa się znacznik końca linii z pliku $ligne, jeśli istnieje
  ...
  // koniec
  return($ligne);
}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // łączy ($login,$pwd) z bazą $dsn
  // zwraca identyfikator połączenia oraz komunikat o błędzie
  ...
}

//nawiązuje połączenie
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // zamyka połączenie o identyfikatorze $connexion
  ...
}

// ---------------------------------------------------------------------------------
function exécuteRequête($connexion, $sql) {
  // wykonuje zapytanie $sql na połączeniu $connexion
  // zwraca tablicę zawierającą 2 elementy ($erreur, $résultat)
 ...
}

Wyniki wyświetlane na ekranie:

Transfert opéré

8.2. Program do obliczania podatku ( impots_04)

W tej wersji wykorzystywana jest klasa Impôts, która pobiera z bazy danych wartości niezbędne do obliczenia podatku. W tym miejscu wprowadzamy nowe pojęcie – zapytanie przygotowane. Wykonanie zlecenia SQL przez SGBD odbywa się w dwóch etapach:

  1. przygotowanie zapytania: program SGBD przygotowuje zoptymalizowany plan wykonania zapytania. Chodzi o to, aby wykonać je w możliwie najbardziej efektywny sposób.
  1. zapytań jest wykonywane.

Jeśli to samo zapytanie zostanie wykonane N razy, powyższe dwa etapy są powtarzane N razy. Możliwe jest jednak przygotowanie zapytania tylko raz, a następnie wykonanie go N razy. W tym celu należy korzystać z zapytań przygotowanych. Jeśli $requête jest poleceniem SQL do wykonania, a $connexion jest obiektem PDO reprezentującym połączenie:

  • $statement=$connexion->prepare($requête) przygotowuje zapytanie i zwraca zapytanie jako „przygotowane”
  • $statement->execute() wykonuje przygotowane zapytanie.

Jeśli przygotowanym zapytaniem jest polecenie select, to

  • $statement->fetchAll() zwraca wszystkie wiersze tabeli wynikowej select w postaci tablicy T, gdzie T[i,j] jest wartością kolumny j w wierszu i tabeli
  • $statement->fetch() zwraca bieżący wiersz tabeli w postaci tablicy T, gdzie T[j] jest wartością kolumny j w wierszu

Zapytanie przygotowane zapewnia nie tylko większą wydajność, ale także inne korzyści. Zapewnia ono w szczególności większe bezpieczeństwo. Dlatego należy je stosować systematycznie.


<?php

// test -----------------------------------------------------
// definicja stałych
$DATA = "data.txt";
$RESULTATS = "resultats.txt";
$TABLE = "impots";
$BASE = "dbimpots";
$USER = "root";
$PWD = "";
$HOTE="localhost";

// dane niezbędne do obliczenia podatku zostały umieszczone w tabeli mysqL $TABLE
// należącej do bazy danych $BASE. Tabela ma następującą strukturę
// limity decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
// parametry podatników (stan cywilny, liczba dzieci, roczne wynagrodzenie)
// zostały umieszczone w pliku tekstowym $DATA, po jednym wierszu na podatnika
// wyniki (stan cywilny, liczba dzieci, roczne wynagrodzenie, podatek do zapłaty) zostały umieszczone w
// plik tekstowy $RESULTATS, po jednym wyniku w każdym wierszu

// tworzy się obiekt „Podatki”
$I = new Impôts($HOTE, $USER, $PWD, $BASE, $TABLE);
// czy wystąpił błąd?
$erreur=$I->getErreur();
if ($erreur) {
  print "$I->erreur\n";
  exit;
}//if
// tworzymy obiekt narzędziowy
$u = new Utilitaires();

// otwieranie pliku z danymi podatników
$data = fopen($DATA, "r");
if (!$data) {
  print "Impossible d'ouvrir en lecture le fichier des données [$DATA]\n";
  exit;
}

// otwieranie pliku wyników
$résultats = fopen($RESULTATS, "w");
if (!$résultats) {
  print "Impossible de créer le fichier des résultats [$RESULTATS]\n";
  exit;
}

// przetwarzamy bieżący wiersz pliku danych podatników
while ($ligne = fgets($data, 100)) {
  // usuwamy ewentualny znacznik końca wiersza
  $ligne = $u->cutNewLineChar($ligne);
  // pobieranie trzech pól: „żonaty”, „dzieci” i „wynagrodzenie”, które tworzą $ligne
  list($marié, $enfants, $salaire) = explode(",", $ligne);
  // oblicza się podatek
  $impôt = $I->calculer($marié, $enfants, $salaire);
  // wpisuje się wynik
  fputs($résultats, "$marié:$enfants:$salaire:$impôt\n");
  // kolejny rekord
}
// zamykamy pliki
fclose($data);
fclose($résultats);

// koniec
exit;

// ---------------------------------------------------------------------------------
// definicja klasy „Podatki”
class Impôts {

  // atrybuty: 3 tabele danych
  private $limites;
  private $coeffR;
  private $coeffN;
  private $erreur;
  private $nbLimites;

  // metoda pobierająca
  public function getErreur(){
    return $this->erreur;
  }
  // konstruktor

  function __construct($HOTE, $USER, $PWD, $BASE, $TABLE) {
    // inicjuje atrybuty $limites, $coeffR, $coeffN
    // dane niezbędne do obliczenia podatku zostały umieszczone w tabeli MySQL o nazwie $TABLE
    // należącej do bazy danych $BASE. Tabela ma następującą strukturę
    // limity decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
    // połączenie z bazą danych MySQL na serwerze $HOTE odbywa się przy użyciu identyfikatora ($USER,$PWD)
    // inicjuje pole $erreur, uwzględniając ewentualny błąd
    // jest puste, jeśli nie wystąpił błąd
    // 
    // połączenie z bazą danych 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;
    }
    // odczytuje tabelę $TABLE
    $requête = "select limites,coeffR,coeffN from $TABLE";
    // wykonuje zapytanie $requête na połączeniu $connexion
    try {
      $statement = $connexion->prepare($requête);
      $statement->execute();
      // przetwarzanie wyniku zapytania
      while ($colonnes = $statement->fetch()) {
        $this->limites[] = $colonnes[0];
        $this->coeffR[] = $colonnes[1];
        $this->coeffN[] = $colonnes[2];
      }
      // brak błędu
      $this->erreur = "";
      // liczba elementów tablicy limitów
      $this->nbLimites = count($this->limites);
    } catch (PDOException $e) {
      $this->erreur = $e->getMessage();
    }
     // rozłączenie
    déconnecte($connexion);
  }
  // --------------------------------------------------------------------------
  function calculer($marié, $enfants, $salaire) {
    // $marié: tak, nie
    // $enfants: liczba dzieci
    // $salaire: roczne wynagrodzenie
    
  // czy przedmiot jest w dobrym stanie?
    if ($this->erreur)
      return -1;

    // liczba udziałów
    ...
  }

}

// ---------------------------------------------------------------------------------
// klasa funkcji pomocniczych
class Utilitaires {

  function cutNewLinechar($ligne) {
    // usuwa się znak końca linii z $ligne, jeśli istnieje
    ...
  }

}
// ---------------------------------------------------------------------------------
function connecte($dsn, $login, $pwd) {
  // łączy ($login,$pwd) z bazą $dsn
  // zwraca identyfikator połączenia oraz komunikat o błędzie
  ...
}

//nawiązuje połączenie
// ---------------------------------------------------------------------------------
function déconnecte($connexion) {
  // zamyka połączenie o identyfikatorze $connexion
  ...
}

Wyniki: takie same jak w przypadku poprzednich wersji.

Komentarze

Nowości znajdują się w wierszach 98–109:

  • wiersz 99: polecenie SQL select, które pozwoli na pobranie danych niezbędnych do obliczenia podatku.
  • wiersz 102: przygotowanie zlecenia SQL select
  • wiersz 103: wykonanie przygotowanego zlecenia
  • wiersze 105–109: przetwarzanie wiersz po wierszu tabeli wynikowej z select
https://stahe.github.io
Ten kurs-samouczek, napisany przez Serge'a Tahé, jest udostępniany publicznie na warunkach Creative Commons Uznanie autorstwa – Bez wykorzystania komercyjnego – Na tych samych warunkach 3.0 bez zmian.