Gestionale

Relazione gestionale ditta Baselli Il gestionale richiesto ha lo scopo di gestire i vari interventi (task es.installazione antenna) che vengono effettuati presso i clienti dalla ditta Baselli che fornisce assistenza elettrica.

Sono di interesse i clienti con i relativi dati anagrafici e gli interventi fatti, dei quali interessano la data di inizio, la data presunta ed effettiva di fine intervento e i materiali utilizzati. Per ogni intervento inoltre registrare i dipendenti coinvolti e il numero di ore di lavoro di ciascuno di essi.

Per ogni dipendente si devono conoscere, oltre ai dati anagrafici, la mansione ed il costo orario.

Per ogni task, alla conclusione di ogni intervento di un singolo dipendente, viene redatto un rapportino riportante, oltre al task, i materiali usati e le ore di lavoro.

Relativamente ai materiali, interessano il fornitore, il tipo, il prezzo di acquisto, il prezzo proposto ai clienti e il deposito in magazzino.

Di seguito le funzioni richieste:

• Creazione/elmininazione/edit dei task che vanno poi assegnati ai dipendenti. Qui si assegnano i dipendenti coinvolti ed il cliente associato al lavoro.

• Creazione /eliminazione/edit dei rapportini di ogni singolo intervento con l’inserimento dei materiali utilizzati (con quantità). Il rapportino è associato al task, quindi va selezionato a quale task fa riferimento.

Dal task, poi sarà possibile generare la fattura, andando a leggere tutti i rapportini, tutti i materiali e le figure professionali coinvolte, con la possibilità di applicare uno sconto in fase di revisione, prima di generare la stampa.

Richieste

• diagramma E-R

• modello logico

• modello fisico

• file SQL di creazione del DB

• risposta alle seguenti select Sql
  • elenco dei task del 2021
  • elenco dei rapportini di un determinato task
  • calcolo delle ore prestate da un determinato dipendente in un mese indicato
  • calcolo dello stipendio di un determinato dipendente in un mese specifico.

Diagramma E-R

Ipotesi

• La data stimata per un intervento viene calcolata con le dati di inizio e fine dei precedenti interventi basandosi sui task.

Modello Logico

Cliente(cf, nome, cognome, nascita)

Dipendente(cf, nome, cognome, nascita, costo_orario, mansione)

Materiale(id, fornitore, tipo, deposito, prezzo_acquisto, prezzo_proposto)

Intervento(id, cf, data_inizio, data_fine) con v.i.r. di cf con cf di Cliente

Materiali(id, id, quantità) con v.i.r. di id con id di Intervento con v.i.r. di id con id di Materiale

Impieghi(id, cf, ore, rapportino, task) con v.i.r. di di con id di Intervento con v.i.r. di cf con cf di Dipendente

Modello Fisico

CREATE DATABASE IF NOT EXISTS Gestionale;

CREATE TABLE Cliente
(
    cf      CHAR(16) PRIMARY KEY,
    nome    VARCHAR(30) NOT NULL,
    cognome VARCHAR(30) NOT NULL,
    nascita DATE        NOT NULL
);

CREATE TABLE Dipendente
(
    cf           CHAR(16) PRIMARY KEY,
    nome         VARCHAR(30) NOT NULL,
    cognome      VARCHAR(30) NOT NULL,
    nascita      DATE        NOT NULL,
    costo_orario INT,
    mansione     VARCHAR(30) NOT NULL
);

CREATE TABLE Materiale
(
    id              INT AUTO_INCREMENT PRIMARY KEY,
    fornitore       VARCHAR(30),
    tipo            VARCHAR(20),
    deposito        INT NOT NULL DEFAULT 0,
    prezzo_acquisto INT NOT NULL,
    prezzo_proposto INT NOT NULL
);

CREATE TABLE Intervento
(
    id          INT AUTO_INCREMENT,
    cf          CHAR(16) REFERENCES Cliente (cf),
    data_inizio DATE NOT NULL DEFAULT GETDATE(),
    data_fine   DATE,
    PRIMARY KEY (id, cf)
);

CREATE TABLE Materiali
(
    idM      INT REFERENCES Materiale (id),
    idI      INT REFERENCES Intervento (id),
    quantità INT NOT NULL DEFAULT 0,
    PRIMARY KEY (idM, idI)
);

CREATE TABLE Impieghi
(
    id         INT REFERENCES Intervento (id),
    cf         CHAR(16) REFERENCES Dipendente (cf),
    ore        INT NOT NULL DEFAULT 0,
    rapportino TEXT,
    task       VARCHAR(30),
    PRIMARY KEY (id, cf)
);

Interrogazioni

Elenco dei task del 2021:

SELECT DISTINCT ip.task 
FROM Impieghi ip, Intervento it
WHERE ip.id=it.id AND YEAR(it.data_inizio)=2021;

Elenco dei rapportini di un determinato task:

SELECT rapportino
FROM Impieghi
WHERE task='task';

Calcolo delle ore prestate da un determinato dipendente in un mese indicato:

SELECT SUM(ip.ore) AS ore_totali_mese
FROM Intervento it, Impieghi ip
WHERE cf='codice_fiscale' AND it.id=ip.id AND YEAR(it.data_inizio)='anno' AND MONTH(data_inizio)='mese'

Calcolo dello stipendio di un determinato dipendente in un mese specifico:

SELECT SUM(ip.ore)*d.costo_orario AS stipendio_mese_dipendente
FROM Intervento it, Impieghi ip, Dipendente d
WHERE cf='codice_fiscale' AND it.id=ip.id AND YEAR(it.data_inizio)='anno' AND MONTH(data_inizio)='mese'