Mostra la font per a Empresa bàsica: compra de clients i compra a proveïdors

=Introducció=
Simularem una botiga bàsica de bicicletes, simplificant-la al màxim. Què fa una botiga de bicicletes?
*Compra bicicletes als seus proveïdors (els diferents fabricants de bicicletes)
*Ven bicicletes als seus clients
*Porta un control del stock/magatzem
Gestionarem tot aquest flux d'informació amb una petita base de dades. Primer, les sentències SQL que gestionen tot aquest funcionament; segon, ho implmentarem mitjançant procediments.

'''NOTA''': quan haguem vist els ''triggers/disparadors'', ho farem amb ''triggers'' (de moment encara no podem fer-ho).
=Base de dades=
[[Fitxer:Bicicleta conor.png | thumbnail]]
Estructura de la base de dades:
<pre>
/*
PROVEIDOR (id_proveidor, proveidor)
CLIENT (id_client, client)
PRODUCTE (id_producte, producte, preu, stock, id_proveidor)
COMPRA_CLIENT (id_client, id_producte, quantitat, data)
COMPRA_PROVEIDOR (id_proveidor, id_producte, quantitat, data)
*/

CREATE DATABASE empresa2 CHARACTER SET utf8 COLLATE utf8_general_ci;
use empresa2;

DROP TABLE IF EXISTS AUDIT;
DROP TABLE IF EXISTS COMPRA_PROVEIDOR;
DROP TABLE IF EXISTS COMPRA_CLIENT;
DROP TABLE IF EXISTS PRODUCTE;
DROP TABLE IF EXISTS CLIENT;
DROP TABLE IF EXISTS PROVEIDOR;

CREATE TABLE PROVEIDOR (
id_proveidor smallint PRIMARY KEY,
proveidor  VARCHAR(50) NOT NULL
);

CREATE TABLE CLIENT (
id_client smallint AUTO_INCREMENT PRIMARY KEY,
client  VARCHAR(50) NOT NULL
);

CREATE TABLE PRODUCTE (
id_producte smallint PRIMARY KEY,
producte VARCHAR(50) NOT NULL,
preu DECIMAL(6,2) NOT NULL,
stock SMALLINT NULL,
id_proveidor smallint NOT NULL,
FOREIGN KEY (id_proveidor) REFERENCES PROVEIDOR(id_proveidor)
);

CREATE TABLE COMPRA_CLIENT (
id_client smallint,
id_producte smallint,
quantitat smallint,
data datetime,
FOREIGN KEY (id_client) REFERENCES CLIENT(id_client),
FOREIGN KEY (id_producte) REFERENCES PRODUCTE(id_producte)
);

CREATE TABLE COMPRA_PROVEIDOR (
id_proveidor smallint,
id_producte smallint,
quantitat smallint,
data datetime,
FOREIGN KEY (id_proveidor) REFERENCES PROVEIDOR(id_proveidor),
FOREIGN KEY (id_producte) REFERENCES PRODUCTE(id_producte)
);

CREATE TABLE AUDIT (
id_audit smallint AUTO_INCREMENT PRIMARY KEY,
audit_info  VARCHAR(255) NOT NULL,
data datetime
);
</pre>
Creem les dades estàtiques (els productes i els proveïdors):
<pre>
INSERT INTO PROVEIDOR VALUES (1,'Conor');
INSERT INTO PROVEIDOR VALUES (2,'Merida');
INSERT INTO PROVEIDOR VALUES (3,'BH');
INSERT INTO PROVEIDOR VALUES (4,'Deporvillage');

# inicialment tenim un stock de 3 unitats de tots els productes
INSERT INTO PRODUCTE VALUES (1,'Bicicleta MTB Deporvillage GR900',659.00,3,4);
INSERT INTO PRODUCTE VALUES (2,'Bicicleta MTB Deporvillage PR500',559.0,3,4);
INSERT INTO PRODUCTE VALUES (3,'Bicicleta MTB Conor - WRC 6700',433.45,3,1);
INSERT INTO PRODUCTE VALUES (4,'Bicicleta MTB Merida Big Nine 60',688.95,3,2);
INSERT INTO PRODUCTE VALUES (5,'Bicicleta MTB Deporvillage SL100 Lady',479.00,3,4);
INSERT INTO PRODUCTE VALUES (6,'Bicicleta MTB Focus Jam 6.9',2896.40,3,2);
INSERT INTO PRODUCTE VALUES (7,'Bicicleta MTB Focus Thron 6.8',2160.65,3,2);
INSERT INTO PRODUCTE VALUES (8,'Bicicleta MTB Haibike Seet 10',799.00,3,1);
INSERT INTO PRODUCTE VALUES (9,'Bicicleta MTB Haibike Seet 9',674.00,3,1);
INSERT INTO PRODUCTE VALUES (10,'Bicicleta MTB Deporvillage GR900',659.00,3,4);
INSERT INTO PRODUCTE VALUES (11,'Bicicleta MTB Focus Whistler 3.6',649.00,3,1);
INSERT INTO PRODUCTE VALUES (12,'Bicicleta MTB Kross Hexagon 6.0',499.95,3,3);
INSERT INTO PRODUCTE VALUES (13,'Bicicleta MTB Kross Hexagon 5.0',449.95,3,3);
INSERT INTO PRODUCTE VALUES (14,'Bicicleta MTB Kross Hexagon 3.0',453.00,3,3);
INSERT INTO PRODUCTE VALUES (15,'Bicicleta MTB BH Spike 2.0',569.90,3,3);
</pre>
Creem les dades dinàmiques, les dades que es van generant amb el temps: els clients, les compres dels clients, i les compres als proveïdors:
<pre>
# compra del client 1
INSERT INTO CLIENT(client) VALUES ('Amy Woodcock');
INSERT INTO COMPRA_CLIENT VALUES (1,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara en queden dos


# compra del client 2
INSERT INTO CLIENT(client) VALUES ('Paolo Randall');
INSERT INTO COMPRA_CLIENT VALUES (2,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara en queden un

# compra del client 3
INSERT INTO CLIENT(client) VALUES ('Israel Mcmillan');
INSERT INTO COMPRA_CLIENT VALUES (3,2,2,NOW());
UPDATE PRODUCTE set stock=stock-2 WHERE id_producte=2; # ara en queden un (n'ha comprat dos)

# compra del client 4, i compra al proveïdor 4
INSERT INTO CLIENT(client) VALUES ('Carlie Webster');
INSERT INTO COMPRA_CLIENT VALUES (4,1,1,NOW());
UPDATE PRODUCTE set stock=stock-1 WHERE id_producte=1; # ara no en queda cap. Hem de comprar aquest model de bici al proveïdor
SELECT * FROM PRODUCTE WHERE id_producte=1; # no en queda cap
INSERT INTO COMPRA_PROVEIDOR VALUES (4,1,3,NOW()); # fem la compra al proveïdor
UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=1;
SELECT * FROM PRODUCTE WHERE id_producte=1; # n'hi torna a haver tres unitats
</pre>

=Programació=
Tot aquest procés de compra de bicicletes per part dels clients, i compra de bicicletes de la botiga als proveïdors, que hem fet manualment, és el que s'ha d'implementar amb programació, mitjançant PROCEDURES. Concretament, hem d'implementar les següent regles, que anomenem '''regles del negoci''' ([https://es.wikipedia.org/wiki/Reglas_de_negocio]):
*S'ha de comprovar que el id_producte existeix.
*Quan quedin 0 o 1 productes en el stock s'ha de fer la compra de 3 unitats al proveïdor.
*S'ha de comprovar que la quantitat > 0, i no es pot fer la compra de x unitats si el número d'unitats que en queden és inferior.
*hauríem de comprovar que el client ja existeix a la base de dades. Si no existeix, s'ha de fer un insert a la taula CLIENT.

Programarem el proceciment ''compraBicileta'' amb diferents versions incrementals, separant el problema en problemes petits.
==Versió 1==
<pre>
# versió 1. Inserció d'un client
DROP PROCEDURE IF EXISTS compraBicicleta_v1;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v1 (
   IN vclient VARCHAR(50)
)
BEGIN
   DECLARE vid_client SMALLINT;
   INSERT INTO CLIENT(client) VALUES (vclient);
   SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   SELECT vid_client;
END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v1('client prova');
</pre>
==Versió 2==
<pre>
# versió 2. Comprovem que el client existeix

DROP PROCEDURE IF EXISTS compraBicicleta_v2;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v2 (
   IN vclient VARCHAR(50)
)
BEGIN
   DECLARE vid_client SMALLINT;
   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   END IF;
   SELECT vid_client;

END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v2('client prova');
</pre>
==Versió 3==
<pre>
# versió 3. El client ja pot comprar una bicicleta, hem de comprovar primer que el id_producte existeix

DROP PROCEDURE IF EXISTS compraBicicleta_v3;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v3 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte INTO vid_producte_temp FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,1,NOW()); # de moment comprem 1 unitat
END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v3('client prova',3);
</pre>
==Versió 4==
<pre>
# versió 4. Ja podem comprar més d'una unitat. Hem de comprovar que quantitat > 0 i que no superi el stock. Ja podem actualitzar el stock.

DROP PROCEDURE IF EXISTS compraBicicleta_v4;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v4 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT,
   IN vquantitat SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;
   DECLARE vstock SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   SELECT CONCAT("stock: ",vstock) AS stock;
   IF (vquantitat<=0 OR vquantitat > vstock) THEN
      SELECT CONCAT("quantitat no vàlida") AS quantitat;
      LEAVE proc_label;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,vquantitat,NOW());
   # actualitzem el stock
   UPDATE PRODUCTE set stock=vstock-vquantitat WHERE id_producte=vid_producte;
   SELECT CONCAT("queden: ",vstock-vquantitat," unitats") as unitats_restants;

END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v4('client prova',3, 3); # nom_client, id_producte, quantitat
CALL compraBicicleta_v4('client prova',4, 3);
</pre>

==Versió 5==
<pre>
# versió 5. Si el stock és <= 1, comprem 3 unitats al proveïdor (primer hem d'avariguar quin és el id_proveidor)

DROP PROCEDURE IF EXISTS compraBicicleta_v5;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v5 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT,
   IN vquantitat SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;
   DECLARE vstock SMALLINT;
   DECLARE vid_proveidor SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   SELECT CONCAT("stock: ",vstock) AS stock;
   IF (vquantitat<=0 OR vquantitat > vstock) THEN
      SELECT CONCAT("quantitat no vàlida") AS quantitat;
      LEAVE proc_label;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,vquantitat,NOW());
   # actualitzem el stock
   UPDATE PRODUCTE set stock=vstock-vquantitat WHERE id_producte=vid_producte;
   SELECT CONCAT("queden: ",vstock-vquantitat," unitats") as unitats_restants;

   IF (vstock-vquantitat)<=1 THEN
      SELECT id_proveidor INTO vid_proveidor FROM PRODUCTE WHERE id_producte=vid_producte;
      SELECT CONCAT("id_proveidor: ",vid_proveidor) as id_proveidor;
      INSERT INTO COMPRA_PROVEIDOR VALUES (vid_proveidor,vid_producte,3,NOW());
      UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=vid_producte;
      SELECT CONCAT("s'ha fet una compra. Ara tenim ",vstock-vquantitat+3," unitats") as unitats;
   END IF;

END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v5('client prova',3,3); # nom_client, id_producte, quantitat
</pre>
==Versió 6==
La taula '''AUDIT''' serveix per fer un ''log'' de tot el que ha passat a dins la base de dades.
<pre>
# versió 6. Inserim la informació d'auditoria a la taula AUDIT.

DROP PROCEDURE IF EXISTS compraBicicleta_v6;

DELIMITER $$
CREATE PROCEDURE compraBicicleta_v6 (
   IN vclient VARCHAR(50),
   IN vid_producte SMALLINT,
   IN vquantitat SMALLINT
)
proc_label:BEGIN
   DECLARE vid_client SMALLINT;
   DECLARE vid_producte_temp SMALLINT;
   DECLARE vstock SMALLINT;
   DECLARE vid_proveidor SMALLINT;

   SELECT id_client INTO vid_client FROM CLIENT WHERE client = vclient;
   IF vid_client IS NULL THEN
      INSERT INTO CLIENT(client) VALUES (vclient);
      SELECT MAX(id_client) INTO vid_client FROM CLIENT;
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha inserit el id_client: ',vid_client),NOW());
   END IF;
   SELECT CONCAT("id_client: ",vid_client) as id_client;

   SELECT id_producte, stock INTO vid_producte_temp, vstock FROM PRODUCTE WHERE id_producte = vid_producte;
   IF vid_producte_temp IS NULL THEN
      SELECT CONCAT("El producte: ",vid_producte, " no existeix a la base de dades") as producte;
      LEAVE proc_label;
   ELSE
      SELECT CONCAT("id_producte: ",vid_producte) AS producte;
   END IF;

   SELECT CONCAT("stock: ",vstock) AS stock;
   IF (vquantitat<=0 OR vquantitat > vstock) THEN
      SELECT CONCAT("quantitat no vàlida") AS quantitat;
      LEAVE proc_label;
   END IF;

   # ja podem fer la compra
   INSERT INTO COMPRA_CLIENT VALUES (vid_client,vid_producte,vquantitat,NOW());
   INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('El client ',vid_client,' ha fet una compra del producte ',vid_producte,' (',vquantitat,' unitats)'),NOW());
   # actualitzem el stock
   UPDATE PRODUCTE set stock=vstock-vquantitat WHERE id_producte=vid_producte;
   SELECT CONCAT("queden: ",vstock-vquantitat," unitats") as unitats_restants;
   INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Del producte ',vid_producte,' en queden ',vstock-vquantitat,' unitats'),NOW());

   IF (vstock-vquantitat)<=1 THEN
      SELECT id_proveidor INTO vid_proveidor FROM PRODUCTE WHERE id_producte=vid_producte;
      SELECT CONCAT("id_proveidor: ",vid_proveidor) as id_proveidor;
      INSERT INTO COMPRA_PROVEIDOR VALUES (vid_proveidor,vid_producte,3,NOW());
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('S''ha realitzat una compra del producte ',vid_producte,' al proveïdor ',vid_proveidor),NOW());
      UPDATE PRODUCTE set stock=stock+3 WHERE id_producte=vid_producte;
      INSERT INTO AUDIT(audit_info,data) VALUES(CONCAT('Ara tenim ',vstock-vquantitat+3,' unitats del producte ',vid_producte),NOW());
      SELECT CONCAT("s'ha fet una compra. Ara tenim ",vstock-vquantitat+3," unitats") as unitats;
   END IF;

END$$

DELIMITER ;
</pre>
<pre>
CALL compraBicicleta_v6('client prova 2',13,1); # nom_client, id_producte, quantitat
</pre>
Contingut de la taula ''AUDIT'' (exemple):
<pre>
mysql> select * from AUDIT;
id_audit	audit_info	data
1	S'ha inserit el client13	2022-04-14 18:45:52
2	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:50
3	Del producte 13 en queden 3 unitats	2022-04-14 18:52:50
4	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:51
5	Del producte 13 en queden 2 unitats	2022-04-14 18:52:51
6	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:52
7	Del producte 13 en queden 1 unitats	2022-04-14 18:52:52
8	S'ha realitzat una compra del producte 13 al proveïdor 3	2022-04-14 18:52:52
9	Ara tenim 4 unitats del producte 13	2022-04-14 18:52:52
10	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:53
11	Del producte 13 en queden 3 unitats	2022-04-14 18:52:53
12	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:54
13	Del producte 13 en queden 2 unitats	2022-04-14 18:52:54
14	El client 10 ha fet una compra del producte 13 (1 unitats)	2022-04-14 18:52:55
15	Del producte 13 en queden 1 unitats	2022-04-14 18:52:55
16	S'ha realitzat una compra del producte 13 al proveïdor 3	2022-04-14 18:52:55
...
</pre>
{{Autor}}, abril 2022