Saverglass — Prépa Entretien

MonProjet.dproj // Fichier projet principal MonProjet.dpr // Programme principal frmPrincipal.pas // Code de la fenêtre principale frmPrincipal.dfm // Définition visuelle (composants) dmDatabase.pas // DataModule (accès données) uCommande.pas // Unité métier (classe TCommande)

Copier program GlassTrack; uses Vcl.Forms, frmPrincipal in 'frmPrincipal.pas' {frmMain}, dmDatabase in 'dmDatabase.pas' {DataModule1: TDataModule}; {$R *.res} begin Application.Initialize; Application.MainFormOnTaskbar := True; Application.CreateForm(TDataModule1, DataModule1); // DataModule en 1er ! Application.CreateForm(TfrmMain, frmMain); Application.Run; end.

Copier unit frmCommandes; interface uses Winapi.Windows, Vcl.Forms, Vcl.Controls, Vcl.StdCtrls, Vcl.DBGrids, Data.DB, FireDAC.Comp.Client; type TfrmCommandes = class(TForm) // Composants déclarés ici (glisser-déposer dans l'IDE) DBGrid1: TDBGrid; // Grille liée aux données btnAjouter: TButton; btnSupprimer: TButton; edtRecherche: TEdit; lblStatut: TLabel; // Gestionnaires d'événements (générés par l'IDE) procedure btnAjouterClick(Sender: TObject); procedure btnSupprimerClick(Sender: TObject); procedure FormCreate(Sender: TObject); procedure edtRechercheChange(Sender: TObject); private FCommandeID: Integer; // Variables internes procedure ChargerCommandes; procedure ValiderSaisie; public property CommandeID: Integer read FCommandeID; end; var frmCommandes: TfrmCommandes; implementation {$R *.dfm} // Lie le fichier visuel .dfm procedure TfrmCommandes.FormCreate(Sender: TObject); begin ChargerCommandes; lblStatut.Caption := 'Prêt'; end; procedure TfrmCommandes.btnAjouterClick(Sender: TObject); begin if not ValiderSaisie then Exit; DataModule1.qryCommandes.Append; // Nouvelle ligne DataModule1.qryCommandes.FieldByName('STATUT').AsString := 'EN_COURS'; DataModule1.qryCommandes.FieldByName('DATE_CMD').AsDateTime := Now; DataModule1.qryCommandes.Post; // Valide en mémoire DataModule1.ApplyUpdates; // Envoie à Oracle end;

Copier -- Séquence pour les IDs (Oracle < 12c) CREATE SEQUENCE SEQ_COMMANDE START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE; CREATE TABLE CLIENTS ( CLIENT_ID NUMBER(10) GENERATED ALWAYS AS IDENTITY, -- Oracle 12c+ NOM VARCHAR2(100) NOT NULL, PAYS VARCHAR2(50), SEGMENT VARCHAR2(20) CHECK (SEGMENT IN ('LUXE','PREMIUM','STANDARD')), CONSTRAINT PK_CLIENT PRIMARY KEY (CLIENT_ID) ); CREATE TABLE COMMANDES ( CMD_ID NUMBER(10) PRIMARY KEY, CLIENT_ID NUMBER(10) NOT NULL, DATE_CMD DATE DEFAULT SYSDATE NOT NULL, STATUT VARCHAR2(20) DEFAULT 'EN_ATTENTE', QUANTITE NUMBER(8) NOT NULL, CONSTRAINT FK_CMD_CLIENT FOREIGN KEY (CLIENT_ID) REFERENCES CLIENTS(CLIENT_ID), CONSTRAINT CHK_STATUT CHECK (STATUT IN ('EN_ATTENTE','EN_COURS','LIVREE','ANNULEE')) ); -- Trigger pour auto-incrément (style Oracle classique) CREATE OR REPLACE TRIGGER TRG_COMMANDE_ID BEFORE INSERT ON COMMANDES FOR EACH ROW BEGIN IF :NEW.CMD_ID IS NULL THEN :NEW.CMD_ID := SEQ_COMMANDE.NEXTVAL; END IF; END;

Copier CREATE OR REPLACE PACKAGE PKG_PRODUCTION AS -- Crée un lot de production, retourne l'ID FUNCTION CREER_LOT( p_cmd_id NUMBER, p_flacon_id NUMBER, p_four_id NUMBER ) RETURN NUMBER; -- Met à jour le résultat du contrôle qualité PROCEDURE VALIDER_QUALITE( p_lot_id NUMBER, p_resultat VARCHAR2, p_defauts NUMBER DEFAULT 0 ); -- Calcule le taux de casse moyen d'un four FUNCTION TAUX_CASSE_FOUR(p_four_id NUMBER) RETURN NUMBER; END PKG_PRODUCTION; / CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTION AS FUNCTION CREER_LOT(p_cmd_id NUMBER, p_flacon_id NUMBER, p_four_id NUMBER) RETURN NUMBER IS v_lot_id NUMBER; BEGIN INSERT INTO LOTS_PRODUCTION (CMD_ID, FLACON_ID, FOUR_ID, DATE_DEBUT) VALUES (p_cmd_id, p_flacon_id, p_four_id, SYSDATE) RETURNING LOT_ID INTO v_lot_id; COMMIT; RETURN v_lot_id; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, 'Erreur création lot: ' || SQLERRM); END; FUNCTION TAUX_CASSE_FOUR(p_four_id NUMBER) RETURN NUMBER IS v_taux NUMBER; BEGIN SELECT AVG(TAUX_CASSE) INTO v_taux FROM LOTS_PRODUCTION WHERE FOUR_ID = p_four_id AND DATE_DEBUT >= ADD_MONTHS(SYSDATE, -3); RETURN NVL(v_taux, 0); END; END PKG_PRODUCTION;

Copier unit dmDatabase; interface uses System.SysUtils, System.Classes, FireDAC.Comp.Client, // TFDConnection, TFDQuery FireDAC.Stan.Param, // TFDParam FireDAC.Phys.OracleDef,// Driver Oracle Data.DB; // TDataSet, TField type TDataModule1 = class(TDataModule) // Composants posés dans l'IDE FDConnection1: TFDConnection; // Connexion Oracle qryCommandes: TFDQuery; // SELECT sur COMMANDES qryLots: TFDQuery; // SELECT sur LOTS_PRODUCTION dsCommandes: TDataSource; // Lien Query → Grid FDTransaction1: TFDTransaction; // Gestion transactions procedure DataModuleCreate(Sender: TObject); procedure DataModuleDestroy(Sender: TObject); private procedure ConfigurerConnexion; public procedure ApplyUpdates; function ExecuterProcedure( const aProcedure: String; const aParams: array of Variant ): Variant; end; var DataModule1: TDataModule1; implementation {$R *.dfm} procedure TDataModule1.DataModuleCreate(Sender: TObject); begin ConfigurerConnexion; // Configuration de la query principale qryCommandes.Connection := FDConnection1; qryCommandes.SQL.Text := 'SELECT c.CMD_ID, c.DATE_CMD, c.STATUT, c.QUANTITE, ' + ' cl.NOM AS CLIENT_NOM ' + 'FROM COMMANDES c ' + 'JOIN CLIENTS cl ON cl.CLIENT_ID = c.CLIENT_ID ' + 'WHERE (:statut IS NULL OR c.STATUT = :statut) ' + 'ORDER BY c.DATE_CMD DESC'; qryCommandes.ParamByName('statut').Clear; // = tous qryCommandes.Open; dsCommandes.DataSet := qryCommandes; end; procedure TDataModule1.ConfigurerConnexion; begin with FDConnection1 do begin DriverName := 'Oracle'; Params.Clear; Params.Add('DriverID=Oracle'); Params.Add('Server=SAVERGLASS_DB'); // TNS alias Params.Add('Database=ORCL'); Params.Add('User_Name=GLASSTRACK'); Params.Add('Password=xxxx'); Params.Add('CharacterSet=AL32UTF8'); LoginPrompt := False; Connected := True; end; end; // Appel d'une procédure/fonction PL/SQL function TDataModule1.ExecuterProcedure( const aProcedure: String; const aParams: array of Variant ): Variant; var qry: TFDQuery; i: Integer; begin qry := TFDQuery.Create(nil); try qry.Connection := FDConnection1; qry.SQL.Text := 'BEGIN :result := ' + aProcedure + '('; for i := 0 to High(aParams) do qry.SQL.Add(':p' + IntToStr(i) + if i<High(aParams) then ',' else ''); qry.SQL.Add('); END;'); qry.Params[0].ParamType := ptOutput; for i := 1 to Length(aParams) do qry.Params[i].Value := aParams[i-1]; qry.ExecSQL; Result := qry.Params[0].Value; finally qry.Free; end; end;

Copier unit uCommandeDAO; // DAO = Data Access Object — séparation logique métier / accès données type TCommandeDAO = class private FConn: TFDConnection; public constructor Create(aConn: TFDConnection); // READ — retourne toutes les commandes filtrées function GetAll(aStatut: String = ''): TFDQuery; // CREATE function Inserer( aClientID: Integer; aQuantite: Integer ): Integer; // retourne le nouvel ID // UPDATE procedure MAJStatut(aCmdID: Integer; aStatut: String); // DELETE procedure Supprimer(aCmdID: Integer); end; function TCommandeDAO.Inserer(aClientID, aQuantite: Integer): Integer; var qry: TFDQuery; begin qry := TFDQuery.Create(nil); try qry.Connection := FConn; qry.SQL.Text := 'INSERT INTO COMMANDES (CLIENT_ID, QUANTITE, STATUT) ' + 'VALUES (:cid, :qty, ''EN_ATTENTE'') ' + 'RETURNING CMD_ID INTO :newid'; // RETURNING = astuce Oracle qry.ParamByName('cid').AsInteger := aClientID; qry.ParamByName('qty').AsInteger := aQuantite; qry.ParamByName('newid').ParamType := ptOutput; qry.ParamByName('newid').DataType := ftInteger; qry.ExecSQL; Result := qry.ParamByName('newid').AsInteger; finally qry.Free; end; end; procedure TCommandeDAO.MAJStatut(aCmdID: Integer; aStatut: String); var qry: TFDQuery; begin qry := TFDQuery.Create(nil); try qry.Connection := FConn; qry.SQL.Text := 'UPDATE COMMANDES SET STATUT = :st WHERE CMD_ID = :id'; qry.ParamByName('st').AsString := aStatut; qry.ParamByName('id').AsInteger := aCmdID; qry.ExecSQL; finally qry.Free; end; end;