8. Ejercicio fiscal con MySQL
Ya hemos escrito tres versiones de este ejercicio. La última versión utilizaba una clase de cálculo de impuestos. Esta clase recuperaba los datos necesarios para el cálculo de un fichero de texto. Ahora los recuperará de una base de datos. Para ello, escribimos un código inicial que transferirá los datos del fichero de texto a una base de datos.
El archivo de texto impots.txt es la siguiente:
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
La base de datos que debe crearse es la siguiente
 |  |
La base de datos se llama [dbimpots] y tiene una única tabla [impots]. A ella accede el usuario [root] sin contraseña.
8.1. Importar un fichero de texto en una tabla MySQL (txt2mysql)
1. <?php
2.
3. // transfers the text file containing the data needed to calculate taxes
4. // into a MySQL table
5. // the data
6. $TAXES = "taxes.txt";
7. $DATABASE = "dbimpots";
8. $TABLE = "taxes";
9. $USER = "root";
10. $PWD = "";
11. $HOST = "localhost";
12.
13. // The data needed to calculate the tax has been placed in the $IMPOTS file
14. // with one line per table in the form
15. // val1:val2:val3,...
16. list($error, $limits, $coeffR, $coeffN) = getTables($TAXES);
17. // Was there an error?
18. if ($error) {
19. print "$error\n";
20. exit;
21. }//if
22. // We transfer these arrays to a MySQL table
23. $error = copyToMysql($limits, $coeffR, $coeffN, $HOST, $USER, $PWD, $BASE, $TABLE);
24. if ($error)
25. print "$error\n";
26. else
27. print "Transfer completed\n";
28. // end
29. exit;
30.
31. // --------------------------------------------------------------------------
32. function copyToMysql($limits, $coeffR, $coeffN, $HOST, $USER, $PWD, $BASE, $TABLE) {
33. // copies the 3 numeric arrays $limits, $coeffR, $coeffN
34. // into the $TABLE table in the $BASE MySQL database
35. // the MySQL database is on the machine $HOST
36. // the user is identified by $USER and $PWD
37. // connection
38. list($error, $connection) = connect("mysql:host=$HOST;dbname=$DATABASE", $USER, $PWD);
39. if ($error)
40. return "Error connecting to MySQL as ($HOST, $USER, $PWD): $error\n";
41. // Delete the table
42. $query = "drop table $TABLE";
43. executeQuery($connection, $query);
44. // create the table
45. $query = "create table $TABLE (limits decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2))";
46. list($error, $res) = executeQuery($connection, $query);
47. if ($error)
48. return "$query: error ($error)";
49. // populate
50. for ($i = 0; $i < count($limits); $i++) {
51. // insert query
52. $query = "insert into $TABLE (limits,coeffR,coeffN) values ($limits[$i],$coeffR[$i],$coeffN[$i])";
53. // execute the query
54. list($error, $res) = executeQuery($connection, $query);
55. // return if error
56. if ($error)
57. return "$query: error ($error)";
58. }//for
59. // Done - disconnect
60. logout($connection);
61. // return without error
62. return "";
63. }
64. // --------------------------------------------------------------------------
65. function getTables($TAXES) {
66. // $IMPOTS: the name of the file containing the data for the $limites, $coeffR, and $coeffN tables
67. ...
68. // end
69. return array("", $limits, $coeffR, $coeffN);
70. }
71. // --------------------------------------------------------------------------
72. function cutNewLinechar($line) {
73. // remove the end-of-line character from $line if it exists
74. ...
75. // end
76. return($line);
77. }
78. // ---------------------------------------------------------------------------------
79. function connect($dsn, $login, $pwd) {
80. // connect ($login, $pwd) to the $dsn database
81. // Returns the connection ID and an error message
82. ...
83. }
84.
85. //connects
86. // ---------------------------------------------------------------------------------
87. function disconnect($connection) {
88. // closes the connection identified by $connection
89. ...
90. }
91.
92. // ---------------------------------------------------------------------------------
93. function executeQuery($connection, $sql) {
94. // executes the $sql query on the $connection
95. // returns an array of 2 elements ($error, $result)
96. ...
97. }
Pantalla resultados:
8.2. El programa de cálculo de impuestos ( impots_04)
Esta versión utiliza un impuesto que recupera de una base de datos los valores necesarios para calcular el impuesto. Aquí introducimos un nuevo concepto: el declaración preparada. La ejecución de una sentencia SQL por un DBMS se produce en dos etapas:
- Se prepara la consulta: el DBMS prepara un plan de ejecución optimizado para la consulta. El objetivo es ejecutarla de la forma más eficiente posible.
- se ejecuta la consulta.
If the same query is executed N times, the two previous steps are performed N times. However, it is possible to prepare the query once and execute it N times. To do this, you must use prepared queries. If $query is the SQL statement to be executed and $connection is the PDO object representing the connection:
- $statement = $connection->prepare($query) prepara una consulta y devuelve la consulta "preparada
- $estatement->ejecutar() ejecuta la consulta preparada.
If the prepared query is a SELECT statement, then
- $statement->fetchAll() devuelve todas las filas de la tabla de resultados de la consulta SELECT como una matriz T, donde T[i,j] es el valor de la columna j en fila i de la mesa
- $statement->fetch() devuelve la fila actual de la tabla como una matriz T, donde T[j] es el valor de la columna j en fila
Las declaraciones preparadas ofrecen ventajas que van más allá de la mera mejora de la eficacia. En particular, proporcionan mayor seguridad. Por lo tanto, deben utilizarse sistemáticamente.
1. <?php
2.
3. // test -----------------------------------------------------
4. // definition of constants
5. $DATA = "data.txt";
6. $RESULTS = "results.txt";
7. $TABLE = "taxes";
8. $DATABASE = "dbimpots";
9. $USER = "root";
10. $PWD = "";
11. $HOST = "localhost";
12.
13. // The data needed to calculate the tax has been placed in the MySQL table $TABLE
14. // belonging to the $BASE database. The table has the following structure
15. // limits decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
16. // the parameters for taxable individuals (marital status, number of children, annual salary)
17. // have been placed in the text file $DATA, with one line per taxpayer
18. // the results (marital status, number of children, annual salary, tax due) are stored in
19. // the text file $RESULTS, with one result per line
20.
21. // we create an Impôts object
22. $I = new Taxes($HOST, $USER, $PWD, $DATABASE, $TABLE);
23. // Was there an error?
24. $error = $I->getError();
25. if ($error) {
26. print "$I->error\n";
27. exit;
28. }//if
29. // create a utilities object
30. $u = new Utilities();
31.
32. // open the taxpayer data file
33. $data = fopen($DATA, "r");
34. if (!$data) {
35. print "Unable to open the data file [$DATA] for reading\n";
36. exit;
37. }
38.
39. // Open the results file
40. $results = fopen($RESULTS, "w");
41. if (!$results) {
42. print "Unable to create the results file [$RESULTS]\n";
43. exit;
44. }
45.
46. // Process the current line of the taxpayer data file
47. while ($line = fgets($data, 100)) {
48. // remove any end-of-line characters
49. $line = $u->cutNewLineChar($line);
50. // retrieve the 3 fields married, children, salary that make up $line
51. list($married, $children, $salary) = explode(",", $line);
52. // calculate the tax
53. $tax = $I->calculate($married, $children, $salary);
54. // print the result
55. fputs($results, "$married:$children:$salary:$tax\n");
56. // next data
57. }
58. // close the files
59. fclose($data);
60. fclose($results);
61.
62. // end
63. exit;
64.
65. // ---------------------------------------------------------------------------------
66. // definition of a Taxes class
67. class Taxes {
68.
69. // attributes: the 3 data arrays
70. private $limits;
71. private $coeffR;
72. private $coeffN;
73. private $error;
74. private $nbLimits;
75.
76. // getter
77. public function getError() {
78. return $this->error;
79. }
80. // constructor
81.
82. function __ construct($HOST, $USER, $PWD, $BASE, $TABLE) {
83. // initializes the $limits, $coeffR, and $coeffN attributes
84. // the data needed to calculate the tax has been placed in the MySQL table $TABLE
85. // belonging to the $BASE database. The table has the following structure
86. // limits decimal(10,2), coeffR decimal(6,2), coeffN decimal(10,2)
87. // the connection to the MySQL database on the $HOST machine is made using the credentials ($USER, $PWD)
88. // initializes the $error field with any error
89. // empty if no error
90. //
91. // connect to the MySQL database
92. $DSN = "mysql:host=$HOST;dbname=$DATABASE";
93. list($error, $connection) = connect($DSN, $USER, $PWD);
94. if ($error) {
95. $this->error = "Error connecting to MySQL as ($HOST, $USER, $PWD): $error\n";
96. return;
97. }
98. // Read the $TABLE table
99. $query = "select limits,coeffR,coeffN from $TABLE";
100. // execute the $query query on the $connection connection
101. try {
102. $statement = $connection->prepare($query);
103. $statement->execute();
104. // process the query result
105. while ($columns = $statement->fetch()) {
106. $this->limits[] = $columns[0];
107. $this->coeffR[] = $columns[1];
108. $this->coeffN[] = $columns[2];
109. }
110. // no error
111. $this->error = "";
112. // number of elements in the limits array
113. $this->limitCount = count($this->limits);
114. } catch (PDOException $e) {
115. $this->error = $e->getMessage();
116. }
117. // disconnect
118. disconnect($connection);
119. }
120. // --------------------------------------------------------------------------
121. function calculate($married, $children, $salary) {
122. // $married: yes, no
123. // $children: number of children
124. // $salary: annual salary
125.
126. // Is the object in a valid state?
127. if ($this->error)
128. return -1;
129.
130. // number of shares
131. ...
132. }
133.
134. }
135.
136. // ---------------------------------------------------------------------------------
137. // a utility class
138. class Utilities {
139.
140. function cutNewLinechar($line) {
141. // remove the end-of-line character from $line if it exists
142. ...
143. }
144.
145. }
146. // ---------------------------------------------------------------------------------
147. function connect($dsn, $login, $pwd) {
148. // Connects ($login, $pwd) to the $dsn database
149. // returns the connection ID and an error message
150. ...
151. }
152.
153. //connect
154. // ---------------------------------------------------------------------------------
155. function disconnect($connection) {
156. // closes the connection identified by $connection
157. ...
158. }
Resultados: igual que en las versiones anteriores.
Comentarios
Las novedades son las líneas 98-109:
- línea 99: el SQL SELECT que recupera los datos necesarios para calcular el impuesto.
- Línea 102: Preparación del SQL SELECT declaración
- Línea 103: Ejecución de la declaración preparada
- Líneas 105-109: procesamiento de la tabla de resultados del declaración SELECT línea por línea