K. Carpeta « api »

Versión para imprimir.

A. api / Bd.php

1
<?php
2

  
3
require_once __DIR__ . "/../libservidorphp/rolBusca.php";

  
4
require_once __DIR__ . "/../libservidorphp/rolAgrega.php";

  
5

  
6
class Bd

  
7
{

  
8

  
9
 private static ?PDO $pdo = null;

  
10

  
11
 static function conexion(): PDO

  
12
 {

  
13
  if (self::$pdo === null) {

  
14

  
15
   self::$pdo = new PDO(

  
16
    // cadena de conexión

  
17
    "sqlite:" . __DIR__ . "/srvamuchos.db",

  
18
    // usuario

  
19
    null,

  
20
    // contraseña

  
21
    null,

  
22
    // Opciones: pdos no persistentes y lanza excepciones.

  
23
    [PDO::ATTR_PERSISTENT => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]

  
24
   );

  
25

  
26
   self::$pdo->exec(

  
27
    'CREATE TABLE IF NOT EXISTS USUARIO (

  
28
      USU_ID INTEGER,

  
29
      USU_SAN TEXT NOT NULL,

  
30
      CONSTRAINT PK_USU PRIMARY KEY(USU_ID),

  
31
      CONSTRAINT UQ_USU_SAN UNIQUE(USU_SAN),

  
32
      CONSTRAINT CHK_USU_SAN CHECK(LENGTH(USU_SAN) > 0)

  
33
     )'

  
34
   );

  
35
   self::$pdo->exec(

  
36
    'CREATE TABLE IF NOT EXISTS ROL (

  
37
      ROL_ID TEXT NOT NULL,

  
38
      ROL_DESCRIPCION TEXT NOT NULL,

  
39
      CONSTRAINT PK_ROL PRIMARY KEY(ROL_ID),

  
40
      CONSTRAINT CHK_ROL_ID CHECK(LENGTH(ROL_ID) > 0),

  
41
      CONSTRAINT UQ_ROL_DESCR UNIQUE(ROL_DESCRIPCION),

  
42
      CONSTRAINT CHK_ROL_DESCR CHECK(LENGTH(ROL_DESCRIPCION) > 0)

  
43
     )'

  
44
   );

  
45
   self::$pdo->exec(

  
46
    'CREATE TABLE IF NOT EXISTS USU_ROL (

  
47
      UR_USU_ID INTEGER NOT NULL,

  
48
      UR_ROL_ID TEXT NOT NULL,

  
49
      CONSTRAINT PK_USU_ROL PRIMARY KEY(UR_USU_ID, UR_ROL_ID),

  
50
      CONSTRAINT FK_UR_USU FOREIGN KEY (UR_USU_ID) REFERENCES USUARIO(USU_ID),

  
51
      CONSTRAINT FK_UR_ROL FOREIGN KEY (UR_ROL_ID) REFERENCES ROL(ROL_ID)

  
52
     )'

  
53
   );

  
54

  
55
   self::$pdo->beginTransaction();

  
56

  
57
   $rolAdministrador = rolBusca(self::$pdo, "Administrador");

  
58
   $rolAdministrador = rolBusca(self::$pdo, "Cliente");

  
59

  
60
   if ($rolAdministrador === false) {

  
61
    rolAgrega(

  
62
     bd: self::$pdo,

  
63
     id: "Administrador",

  
64
     descripcion: "Administra el sistema."

  
65
    );

  
66
   }

  
67

  
68
   if (rolBusca(self::$pdo, "Cliente") === false) {

  
69
    rolAgrega(

  
70
     bd: self::$pdo,

  
71
     id: "Cliente",

  
72
     descripcion: "Realiza compras."

  
73
    );

  
74
   }

  
75

  
76
   self::$pdo->commit();

  
77
  }

  
78

  
79
  return self::$pdo;

  
80
 }

  
81
}

  
82

 


      

     

         
B. api / rolCheckboxes.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/Bd.php";

  
4

  
5
function rolCheckboxes()

  
6
{

  
7
 $bd = Bd::conexion();

  
8
 $lista =

  
9
  $bd->query("SELECT * FROM ROL ORDER BY ROL_ID")->fetchAll(PDO::FETCH_ASSOC);

  
10

  
11
 $render = "<legend>Roles</legend>";

  
12
 foreach ($lista as $modelo) {

  
13
  $id = htmlentities($modelo["ROL_ID"]);

  
14
  $descripcion = htmlentities($modelo["ROL_DESCRIPCION"]);

  
15
  $render .=

  
16
   "<p>

  
17
    <label style='display: flex'>

  
18
     <input type='checkbox' name='rolIds[]' value='$id'>

  
19
     <span>

  
20
      <strong style='display: block'>$id</strong>

  
21
      $descripcion

  
22
     </span>

  
23
    </label>

  
24
   </p>";

  
25
 }

  
26

  
27
 return $render;

  
28
}

  
29

 


      

     

         
C. api / usuario-agrega.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/recibeTextoObligatorio.php";

  
5
require_once __DIR__ . "/../libservidorphp/recibeArray.php";

  
6
require_once __DIR__ . "/../libservidorphp/usuRolAgrega.php";

  
7
require_once __DIR__ . "/../libservidorphp/devuelveCreated.php";

  
8
require_once __DIR__ . "/Bd.php";

  
9
require_once __DIR__ . "/rolCheckboxes.php";

  
10

  
11
$san = recibeTextoObligatorio("san");

  
12
$rolIds = recibeArray("rolIds");

  
13

  
14
$bd = Bd::conexion();

  
15
$bd->beginTransaction();

  
16

  
17
$stmt = $bd->prepare(

  
18
 "INSERT INTO USUARIO (

  
19
    USU_SAN

  
20
   ) values (

  
21
    TRIM(:USU_SAN)

  
22
   )"

  
23
);

  
24
$stmt->execute([

  
25
 ":USU_SAN" => $san

  
26
]);

  
27
$usuId = $bd->lastInsertId();

  
28

  
29
usuRolAgrega($bd, $usuId, $rolIds);

  
30

  
31
$bd->commit();

  
32

  
33
$encodeUsuId = urlencode($usuId);

  
34
$query = http_build_query(["id" => $usuId]);

  
35
devuelveCreated("/api/usuario-vista-modifica.php?$query", [

  
36
 "id" => ["value" => $usuId],

  
37
 "san" => ["value" => $san],

  
38
 "roles" => ["innerHTML" =>  rolCheckboxes()],

  
39
 "rolIds[]" => ["value" => $rolIds],

  
40
]);

  
41

 


      

     

         
D. api / usuario-elimina.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/recibeEnteroObligatorio.php";

  
5
require_once __DIR__ . "/../libservidorphp/devuelveNoContent.php";

  
6
require_once __DIR__ . "/Bd.php";

  
7
require_once __DIR__ . "/usuRolElimina.php";

  
8

  
9
$usuId = recibeEnteroObligatorio("id");

  
10

  
11
$bd = Bd::conexion();

  
12
$bd->beginTransaction();

  
13

  
14
usuRolElimina($bd, $usuId);

  
15

  
16
$stmt = $bd->prepare("DELETE FROM USUARIO WHERE USU_ID = :USU_ID");

  
17
$stmt->execute([":USU_ID" => $usuId]);

  
18

  
19
$bd->commit();

  
20

  
21
devuelveNoContent();

  
22

 


      

     

         
E. api / usuario-modifica.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/recibeEnteroObligatorio.php";

  
5
require_once __DIR__ . "/../libservidorphp/recibeTextoObligatorio.php";

  
6
require_once __DIR__ . "/../libservidorphp/recibeArray.php";

  
7
require_once __DIR__ . "/../libservidorphp/usuRolAgrega.php";

  
8
require_once __DIR__ . "/../libservidorphp/devuelveJson.php";

  
9
require_once __DIR__ . "/Bd.php";

  
10
require_once __DIR__ . "/usuRolElimina.php";

  
11
require_once __DIR__ . "/rolCheckboxes.php";

  
12

  
13
$usuId = recibeEnteroObligatorio("id");

  
14
$san = recibeTextoObligatorio("san");

  
15
$rolIds = recibeArray("rolIds");

  
16

  
17
$bd = Bd::conexion();

  
18
$bd->beginTransaction();

  
19

  
20
$stmt = $bd->prepare(

  
21
 "UPDATE USUARIO

  
22
   SET

  
23
    USU_SAN = TRIM(:USU_SAN)

  
24
   WHERE

  
25
    USU_ID = :USU_ID"

  
26
);

  
27
$stmt->execute([

  
28
 ":USU_SAN" => $san,

  
29
 ":USU_ID" => $usuId,

  
30
]);

  
31

  
32
usuRolElimina($bd, $usuId);

  
33
usuRolAgrega($bd, $usuId, $rolIds);

  
34

  
35
$bd->commit();

  
36

  
37
devuelveJson([

  
38
 "id" => ["value" => $usuId],

  
39
 "san" => ["value" => $san],

  
40
 "roles" => ["innerHTML" =>  rolCheckboxes()],

  
41
 "rolIds" => ["value" => $rolIds],

  
42
]);

  
43

 


      

     

         
F. api / usuario-vista-agrega.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/devuelveJson.php";

  
5
require_once __DIR__ . "/rolCheckboxes.php";

  
6

  
7
devuelveJson(["roles" => ["innerHTML" =>  rolCheckboxes()]]);

  
8

 


      

     

         
G. api / usuario-vista-index.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/devuelveJson.php";

  
5
require_once __DIR__ . "/Bd.php";

  
6

  
7
$bd = Bd::conexion();

  
8
$stmt = $bd->query(

  
9
 "SELECT

  
10
    U.USU_ID,

  
11
    U.USU_SAN,

  
12
    GROUP_CONCAT(UR.UR_ROL_ID, ', ') AS roles

  
13
   FROM

  
14
    USUARIO U LEFT JOIN USU_ROL UR

  
15
     ON U.USU_ID = UR.UR_USU_ID

  
16
   GROUP BY

  
17
    U.USU_SAN

  
18
   ORDER BY

  
19
    U.USU_SAN"

  
20
);

  
21
$lista = $stmt->fetchAll(PDO::FETCH_ASSOC);

  
22

  
23
$render = "";

  
24
foreach ($lista as $modelo) {

  
25
 $usuId = $modelo["USU_ID"];

  
26
 $query = htmlentities(http_build_query(["id" => $usuId]));

  
27
 $urlModifica = "modifica.html?$query";

  
28
 $usuSan = htmlentities($modelo["USU_SAN"]);

  
29
 $roles = $modelo["roles"] === null || $modelo["roles"] === ""

  
30
  ? "<em>-- Sin roles --</em>"

  
31
  : htmlentities($modelo["roles"]);

  
32
 $render .=

  
33
  "<dt><a href='$urlModifica'>$usuSan</a></dt>

  
34
   <dd><a href='$urlModifica'>$roles</a></dd>";

  
35
}

  
36

  
37
devuelveJson(["lista" => ["innerHTML" => $render]]);

  
38

 


      

     

         
H. api / usuario-vista-modifica.php

 
  
 
 
  
1
<?php

  
2

  
3
require_once __DIR__ . "/../libservidorphp/manejaErrores.php";

  
4
require_once __DIR__ . "/../libservidorphp/recibeEnteroObligatorio.php";

  
5
require_once __DIR__ . "/../libservidorphp/validaEntidadObligatoria.php";

  
6
require_once __DIR__ . "/../libservidorphp/devuelveJson.php";

  
7
require_once __DIR__ . "/../libservidorphp/rolIdsParaUsuId.php";

  
8
require_once __DIR__ . "/Bd.php";

  
9
require_once __DIR__ . "/rolCheckboxes.php";

  
10

  
11
$usuId = recibeEnteroObligatorio("id");

  
12

  
13
$bd = Bd::conexion();

  
14

  
15
$stmt = $bd->prepare("SELECT * FROM USUARIO WHERE USU_ID = :USU_ID");

  
16
$stmt->execute([":USU_ID" => $usuId]);

  
17
$modelo = $stmt->fetch(PDO::FETCH_ASSOC);

  
18

  
19
$modelo = validaEntidadObligatoria("Usuario",  $modelo);

  
20

  
21
$rolIds = rolIdsParaUsuId(Bd::conexion(), $usuId);

  
22

  
23
devuelveJson([

  
24
 "id" => ["value" => $usuId],

  
25
 "san" => ["value" => $modelo["USU_SAN"]],

  
26
 "roles" => ["innerHTML" =>  rolCheckboxes()],

  
27
 "rolIds[]" => $rolIds

  
28
]);

  
29

 


      

     

         
I. api / usuRolElimina.php

 
  
 
 
  
1
<?php

  
2

  
3
function usuRolElimina(\PDO $bd, string $usuId)

  
4
{

  
5
 $usuRolElimina =

  
6
  $bd->prepare("DELETE FROM USU_ROL WHERE UR_USU_ID = :UR_USU_ID");

  
7
 $usuRolElimina->execute([":UR_USU_ID" => $usuId]);

  
8
}

  
9