Tema

Una Società di riparazione di apparecchi telefonici e televisivi desidera commissionare ad una software house la realizzazione di un sistema informativo per la gestione delle richieste di riparazione (apertura del TICKET di riparazione) degli articoli trattati da parte dei negozi specializzati (rivendite o riparatori, denominati PDA, Punti Di Accettazione).

Il processo di gestione prevede la memorizzazione di tutte le informazioni contenute nel modulo cartaceo utilizzato abitualmente dai PDA: codice identificativo dell’intervento (intero progressivo generato in automatico e non modificabile), codice del PDA, nominativo e recapito telefonico e/o e-mail del cliente, data di invio della richiesta di intervento, tipologia, marca e modello dell’apparecchio telefonico/televisivo, difetto o anomalia segnalata, ecc…

L’accesso al nuovo sistema informativo, verrà regolamentato mediante l’inserimento di username e password, in modo da garantire un accesso sicuro. Gli utenti potranno usufruire di alcune funzionalità: compilazione di un nuovo modulo di prenotazione on line, visualizzazione dello stato dei ticket (APERTO: il riparatore ha preso in carico la richiesta; IN_RIPARAZIONE: l’articolo è in riparazione; CHIUSO: l’articolo, riparato o non riparato, è pronto per la consegna al PDA), visualizzazione del tempo residuo stimato per la conclusione dell’intervento stesso.

Il candidato, formulate le opportune ipotesi aggiuntive, realizzi:

• Una analisi della realtà di riferimento, completa dello schema funzionale dell’architettura proposta.

• Uno schema concettuale ed uno schema logico del data base.

• La definizione delle relazioni e le seguenti interrogazioni espresse in linguaggio SQL:

1. visualizzare l’elenco, in ordine cronologico, di tutti gli interventi di riparazione di telefoni cellulari del tipo “sostituzione display” effettuati nell’arco di un mese;
2. visualizzare i dati dei clienti ai quali, dopo trenta giorni, non è stato ancora riparato l’articolo consegnato;
3. data una marca ed un modello, visualizzare la durata media degli interventi di riparazione per i prodotti di tale marca e modello;
4. calcolare e visualizzare quanti interventi di riparazione, andati a buon fine, sono stati effettuati, suddivisi per marca, nell’arco di un anno;
5. calcolare e visualizzare quanti TICKET sono stati compilati da ciascun PDA e la durata media di lavorazione dei TICKET;
6. dato il codice identificativo di un intervento, visualizzarne lo stato.
7. • La codifica, in un linguaggio di programmazione per il web, di un segmento significativo del progetto realizzato.

___________________________

Durata massima della prova: 6 ore.

È consentito soltanto l’uso di manuali tecnici e di calcolatrici non programmabili.

Non è consentito lasciare l’Istituto prima che siano trascorse 3 ore dalla dettatura del tema

Analisi 

Si deve realizzare un portale web per la gestione delle riparazioni di apparecchi elettronici(TV o cellulari). Questo portale viene usato dai centri di assistenza che permetta per memorizzare i dati relativi alla presa incarico di una riparazione e per segnalare lo stato di avanzamento degli utenti. Un utente (previa registrazione al portale può prenotare un intervento oppure consultare ticket già aperti. Il sistema dovrà gestire diverse tipologie di utenti (clienti, amministratore , riparatore ognuno dei quali avrà privilegi diversi). Il tutto sarà realizzato tramite un linguaggio di script lato server (PHP), e da un linguaggio lato client per la visualizzazione e l’inserimento dei dati(HTML CSS). I dati saranno memorizzati in un database Mysql. 

Modello E/R

Vediamo le entità e le associazioni coinvolte con i relativi attributi.

Entità

Abbiamo l’entità utente con gli attributi username password email e profilo per distinguere i diversi tipi di utenti.

l’entità Cliente identificata dal codice fiscale dal nome cognome indirizzo telefono.

l’entità ticket che dovrà registrare un intero progressivo per ogni ticket, da data di apertura la data di chiusura lo stato del ticket(aperto, chiuso riparato,chiuso non riparato) il tipo di intervento richiesto il tipo di apparecchio preso in carico.

L’entità centro di assistenza prevede due sotto entità riparatori e venditori. come attributi codice responsabile indirizzo telefono sito web indirizzo email

L’entità apparecchio codice tipo marca modello anno

Associazioni

tra Utente e Ticket l’associazione Consulta con attributo ora e data N:M

Regola di lettura

Ogni Utente può consultare uno o più ticket

Ogni ticket può essere Consultato da uno o più Utente

tra Cliente e Ticket l’associazione Aprire 1:N

Regola di lettura

Consideriamo cliente chi ha aperto almeno un ticket

Ogni Cliente deve aprire uno o più ticket

Ogni ticket deve essere Aperto da uno o più Clienti

tra Pda e Ticket l’associazione Gestisce 1:N

Regola di lettura

Ogni Pda può Gestire  uno o più ticket

Ogni ticket deve essere gestito  da un Pda

tra Apparecchio e Ticket l’associazione  Associa 1:N

Regola di lettura

Ogni Apparecchio è Associato a uno o più ticket

A Ogni ticket è Associato un Apparecchio

Ristrutturazione Modello E/R

Pda è un generalizzazione totale e l’accorpiamo verso l’alto inserento un attributo tipo. I camp indirizzi sono campi composti che scomponiamo in via civico cap e città per i pad  invece creiamo un’entità indirizzo per i clienti con associazione 1:N supponendo che clienti della stessa famiglia possano aprire Ticket

 

 

Modello Relazionale

Ad ogni entità Corrisponde una Relazione e nelle associazioni 1 a molti la chiave primaria di uno diventa chiave esterna di molti

Apparecchio(codice(pk),marca,modello,tipo,anno)

Utente(usename(pk),password,email,profilo)

Cliente(codfisc(PK),cognome,nome,telefono,email,indirizzo(FK))

Pda(codice(PK),nome,responsabile,telefono,fax,email,sito,via,civico,città, cap)

Consulta(utente(PK),ticket(PK)dataOra(PK))

Ticket(codice(PK),tipoIntervento,stato,dataApertura,dataChiusura,tempoStimato,pda(FK).cliente(fk),apparecchio(FK))

 

Relazione	Attributi	Chiave	Formato	Dimen.	Null	Vincolo
Clienti	codFisc	pk	char	16	NO	chiave
	cognome		varchar	20	no
	nome		varchar	20	no
	telefono		char	10
	email		varchar	20
	indirizzo	FK	intero			Indirizzi.codice
Utenti	username	PK	varchar	10	no	chiave
	password		varchar	10	no
	email		varchar	20	no
	profilo		Enum		no	amministratore , cliente, ospite, riparatore
Consulta	utente	PK	varchar	10	no	Utenti.user
	ticket	pk	intero		no	Ticket.codice
	data	pk	data		no
Ticket	codice	PK	intero		no	auto incremento
	tipoIntervento		Testo		no
	dataApertura		data		no
	dataCiusura		data			maggiore dataApertura
	stato		Enum		no	aperto, chiuso riparato,chiuso non riparato
	tempoStimato		varchar	10
	pda	fk	intero		no	Pda.codice
	cliente	fk	char	16	no	Clienti.codfisc
	apparecchio	fk	varchar	50	no	Apparecchi.codice
Apparecchi	codice	pk	varchar	10	no
	marca		varchar	10	no
	modello		varchar	10
	anno		char	4
	tipo		Enum		no	cellulare, televisore
Pda	codice	pk	intero		no	auto incremento
	responsabile		varchar	20	no
	nome		varchar	20	no
	sito		varchar	50
	email		varchar	20	no
	telefono		char	10	no
	fax		char	10
	tipo		Enum		no	venditore, riparatore
	via		varchar	20	no
	civico		varchar	6
	città		VARCHAR	10	NO
	cap		char	5
Indirizzi	codice	pk	intero		no	auto incremento
	via		varchar	10	no
	civico		varchar	5
	città		VARCHAR	10	NO
	cap		char	5	no

Modello fisico

Creiamo il database e le tabelle che non posseggono una chiave esterna

CREATE DATABASE Riparazioni;

CREATE TABLE Indirizzi ( codice int AUTO_INCREMENT PRIMARY KEY,
                        via varchar(20) NOT NULL, 
                        civico int,
                        cap char(5) NOT NULL,
                        città varchar(10) NOT NULL
)ENGINE=INNODB;

CREATE TABLE utenti(username varchar(10) PRIMARY KEY,
                    password varchar(10) NOT NULL ,
                    email varchar(20) NOT NULL,
                    profilo ENUM ('amministratore','cliente','tecnico') DEFAULT 'cliente' 
)ENGINE=INNODB;

CREATE TABLE Indirizzi(codice int AUTO_INCREMENT PRIMARY KEY, 
                       via varchar(10) NOT NULL , 
                       civico varchar(5), 
                       città varchar(10) NOT NULL,
                       cap char(5) NOT NULL 
)ENGINE=INNODB;

CREATE TABLE Apparecchi (
codice varchar(10) PRIMARY KEY,
marca varchar(10) NOT NULL, 
modello varchar(10),
anno char(4),
 tipo Enum ('cellulare',televisore') NOT NULL) ENGINE=INNDB;

CREATE TABLE `Pda` (
  `codice` int(11) NOT NULL,
  `responsabile` varchar(20) NOT NULL,
  `nome` varchar(10) NOT NULL,
  `sito` varchar(50) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `telefono` char(10) NOT NULL,
  `fax` char(10) DEFAULT NULL,
  `tipo` enum('CELLULARE','TV') NOT NULL,
  `via` varchar(20) NOT NULL,
  `civico` varchar(6) DEFAULT NULL,
  `città` varchar(19) NOT NULL,
  `cap` char(5) NOT NULL
) ENGINE=InnoDB

Ora creiamo le rimanenti tabelle

CREATE TABLE Clienti(
    codFisc CHAR(16) PRIMARY KEY,
    cognome VARCHAR(20) NOT NULL,
    nome VARCHAR(20) NOT NULL,
    telefono CHAR(10),
    email VARCHAR(20),
    indirizzo INT,
    FOREIGN KEY(indirizzo) REFERENCES Indirizzi(codice)
) ENGINE = INNODB;

CREATE TABLE Ticket(
   
    codice INT AUTO_INCREMENT PRIMARY KEY,
    tipoIntervento TEXT NOT NULL,
    dataApertura DATE NOT NULL,
    dataCiusura DATE ,
    stato ENUM(
        'aperto',
        'chiuso riparato',
        'chiuso non riparato'
    ) DEFAULT 'aperto',
    tempoStimato VARCHAR(10),
    pda INT not null,
    cliente char (16) not null,
    apparecchio CHAR(10) not null,
    FOREIGN KEY (pda) REFERENCES Pda(codice),
    FOREIGN KEY (cliente) REFERENCES Clienti(codFisc),
    FOREIGN KEY (apparecchio)  REFERENCES Apparecchi(codice)
   CHECK(dataChiusura>dataApertura)
  
) ENGINE = INNODB;

CREATE TABLE Consulta(
    utente VARCHAR(10) NOT NULL,
    ticket INT,
    DATA DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(utente, ticket, DATA),
   FOREIGN KEY (`utente`) REFERENCES `Utenti` (`username`),
   FOREIGN KEY (`ticket`) REFERENCES `Ticket` (`codice`)
) ENGINE = INNODB;

SQL Query

1. visualizzare l’elenco, in ordine cronologico, di tutti gli interventi di riparazione di telefoni cellulari del tipo “sostituzione display” effettuati nell’arco di un mese;

SELECT
    *
FROM
    `Apparecchi`
JOIN Ticket ON Apparecchi.codice = Ticket.apparecchio
WHERE
    Apparecchi.tipo = "cellulare" AND Ticket.tipoIntervento = "sostituzione dispaly" AND Ticket.dataApertura >=[start] AND Ticket.dataCiusura <=[end]
ORDER BY
    Ticket.dataApertura

1. visualizzare i dati dei clienti ai quali, dopo trenta giorni, non è stato ancora riparato l’articolo consegnato;

SELECT
    Clienti.codFisc,
    cognome,
    nome
FROM
    Clienti
JOIN Ticket ON Clienti.codFisc = Ticket.cliente
WHERE
    Ticket.stato = "aperto" AND DATEDIFF(Ticket.dataApertura,CURRENT_DATE) > 30

1. data una marca ed un modello, visualizzare la durata media degli interventi di riparazione per i prodotti di tale marca e modello;

SELECT
    AVG(
        DATEDIFF(
            Ticket.dataApertura,
            Ticket.dataCiusura
        )
    ) AS media
FROM
    Apparecchi
JOIN Ticket ON Apparecchi.codice = Ticket.apparecchio
WHERE
    (
        Ticket.stato = "chiso" OR Ticket.stato = "chiuso non riparato"
    ) AND Apparecchi.marca= [marca] AND Apparecchi.modello = [modello]

 

1. calcolare e visualizzare quanti interventi di riparazione, andati a buon fine, sono stati effettuati, suddivisi per marca, nell’arco di un anno;

SELECT
    marca COUNT(*) AS numeriInterventi
FROM
    Apparecchi
JOIN Ticket ON Apparecchi.codice = Ticket.apparecchio
WHERE
    Ticket.stato = "ciuso" AND Ticket.dataApertura > [ START ] AND Ticket.dataCiusura < [
END ]
GROUP BY
    marca

 

1. calcolare e visualizzare quanti TICKET sono stati compilati da ciascun PDA e la durata media di lavorazione dei TICKET;

SELECT
    Pda.codice,
    nome, COUNT(*) AS numeriInterventi,
    AVG(
        DATEDIFF(
            Ticket.dataApertura,
            Ticket.dataCiusura
        )
    ) AS durataMedia
FROM
    Pda
JOIN Ticket ON Pda.codice = Ticket.pda
WHERE
    Ticket.stato = "chiuso"
ORDER BY
    Pda.codice

 

1. dato il codice identificativo di un intervento, visualizzarne lo stato.

SELECT
    stato
FROM
    Ticket
WHERE
    codice = [ ]

Codifica

Il problema lascia libera scelta di quale parte del sito web da realizzare. Codifichiamo la parte in ui un utente voglia visualizzare lo stato del suo ticket per far questo interagisce con una form html dove dovrà inserire il numero di ticket e che invierà i dati tramite post alla pagina ticket.php che elabora la richiesta.

ticket.html

<!DOCTYPE html>
<html lang="it">
<head>
    <meta charset="UTF-8">
    <title> Cerca ticket</title>
</head>
<body>
<h1>Inserisci il codice del tuo ticket</h1>
<form action="ticket.php" method="post">
    <label>Codice ticket</label> <input type="text" name="coldice" required placeholder="Codice ticket">
    <br>
    <input type="submit" name="invia">
</form>
</body>
</html>

ticket.php

<? php //Segnalatore di inzio codice PHP
/****************************************/
/* Valorizzazione parametri del database */
/****************************************/
session_start();
$user=$_SESSION['user'];
$codice=$_POST['codice'];

$db_host = "localhost"; // IP ADDRESS del database server che ospita i dati
$db_user = "root";
$db_pswd = "1234"; //per semplicità la pswd è in chiaro e non crittografata
$db_name = "tiparazioni";
/****************************************/
/* Connessione al database */
/****************************************/
$connessione = mysqli_connect($db_host, $db_user, $db_pswd,$db_name)
or die("Connessione non riuscita" . mysqli_connect_error());
/****************************************/
/* Selezione del database */
/****************************************/

/****************************************/
/* Preparazione della query SQL */
/****************************************/
// QUERY Q6: occorre costruire nella variabile $query, secondo la sintassi SQL, la query di interesse che dato un codice ticket
// ne visualizzi lo stato
$query=" SELECT * FROM Ticket WHERE codice = $codice";
/****************************************/
/* Esecuzione di una query SQL */
/****************************************/
$risultato = mysqli_query($connessione,$query) or die("Query fallita" . mysqli_error());
/****************************************/
/* Calcolo del numero di occorrenze */
/****************************************/
$numrighe=mysqli_num_rows($risultato);
/***********************************************************************************************/
/* Visualizzazione delle occorrenze in una pagina HTML usando i relativi TAG in modo embedded (funzione “echo”)*/
/***********************************************************************************************/
if ($numrighe == 1)
{
    $q="insert into Consulta ('utente','ticket','data') values ($utente,$codice,CURRENT_TIMESTAMP )";
    $r=mysqli_query($connessione,$q) or die ("errore di inserimento")
while($a=mysqli_fetch_array($risultato,MYSQLI_ASSOC);
    foreach ($a as $key=>$valore )
        echo $key.":   ".$valore."<br>";
        
}
else
{
    echo "nessun tiket trovato";
}
/********************************************/
/* Liberazione delle risorse del risultato della query */
/********************************************/
mysqli_free_result($risultato) or die("Liberazione risorse fallita");
mysqli_free_result($q) or die("Liberazione risorse fallita");;
/*****************************************/
/* Chiusura della connessione al database */
/*****************************************/
mysqli_close($connessione) or die("Chiusura connessione fallita");
?> //Segnalatore di fine codice PHP