Outils pour la gestion d'un restaurant

Les champs sur fond blanc sont complétés par l'utilisateur, les autres sont calculés par le programme.

La partie en gris foncé (la liste des ingrédients) est un sous-formulaire qui fonctionne suivant la technique père-fils. (Pour plus de détails sur la technique, référerez-vous à ce tutoriel.)

L'image (dynamique) du plat n'est pas incluse dans la base de données. Les fichiers idPlat.jpg sont logés dans un sous-répertoire (« Images ») de celui qui contient la base de données. (La technique est décrite dans le tutoriel de cafeine : Gestion de photos par formulaire.)

II-A-1. Fonctionnalités▲

La taille du sous-formulaire s'ajuste en fonction du nombre d'ingrédients. (Pour la technique voir le § IV de ce tutoriel de comptabilité.)

Les ingrédients mentionnés en doublon sont allumés en rouge.

Voici comment.

1° D'abord une fonction

 

Sélectionnez

Public Function Doublon(Plat As Long, Ingredient As Long) As Boolean
  Doublon = DCount("*", "tblPlatsIngredients", "PlatFK=" & Plat & " and IngredientFK =" & Ingredient) > 1
End Function

On donne en paramètres les identificateurs du plat et de l'ingrédient.
On compte dans tblPlatsIngredients le nombre d'enregistrements qui répondent à ces critères, s'il y en a plus qu'un, la comparaison >1 donne True et la fonction renvoie cette valeur : il y a doublon.

2° Dans SF_RecettesIng, nous avons ajouté un contrôle non visible :

Ce contrôle aura donc la valeur False ou True selon que txtPlatFK de l'enregistrement actif est unique ou en doublon.

3° Et enfin, on exploite ce critère dans la mise en forme conditionnelle de cboIngredientFK

Lors de la confection d'une nouvelle recette, l'utilisateur peut importer, en un clic, les ingrédients d'une recette existante prise comme modèle. Voici le code :

 

Sélectionnez

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.

Private Sub btnAjoutIng_Click()
            
  Dim strSQL As String
  If IsNull(Me.cboRecette) Then
      MsgBox "Vous devez d'abord choisir une recette !"
      DoCmd.GoToControl "cboRecette"
      Me.cboRecette.Dropdown
      Exit Sub
  End If
            
  Me.Refresh 'pour enregistrer la recette en cours
            
  'Ajouter les ingrédients du modèle
  DoCmd.SetWarnings False
  strSQL = "INSERT INTO tblPlatsIngredients ( PlatFK, IngredientFK, Quantite ) " _
         & "SELECT  " & Forms!F_Recettes!txtPlatPK _
              & ", tblPlatsIngredients.IngredientFK, tblPlatsIngredients.Quantite " _
         & "FROM tblPlatsIngredients " _
         & "WHERE tblPlatsIngredients.PlatFK=" & Me.cboRecette & ";"
  CurrentDb.Execute (strSQL)
  DoCmd.SetWarnings True
            
  CtnrSF_RecettesIng.Requery
            
  'Remoduler la taille du sous-formulaire
  Call AmenagerTailleSF(Me.Name, "CtnrSF_RecettesIng")
            
End Sub

Commentaire du code

4-9 : on vérifie qu'un modèle de recette a été choisi. Sinon, on invite à le faire.

14-21 : on construit à la volée le SQL d'une requête qui va ajouter
- dans tblPlatsIngredients,
- pour la recette en cours,
- les ingrédients du modèle choisi.

Voici l'image de la requête qui serait construite, si on demandait d'ajouter les ingrédients de « Soupe à l'oignon » dans « Filets de rougets » (beurk !)

23-26 : on actualise le sous-formulaire fils et on ajuste sa taille.

Un double-clic sur la photo miniature du plat provoque l'affichage de celle-ci dans sa dimension originale en utilisant le logiciel que l'utilisateur a associé à l'extension « jpg » (pour autant que son navigateur soit Explorer).

 

Sélectionnez

Private Sub iPhotoPlat_DblClick(Cancel As Integer)
Shell "C:\WINDOWS\EXPLORER.EXE " & CurrentProject.Path & "\Images\" & Me.txtPlatPK & ".jpg"
End Sub

N.B. Les images sont toutes logées dans le sous-répertoire « Images » du dossier qui contient la base de données. Leur nom est composé de l'identificateur du plat, suivi de l'extension « .jpg ».

Si un prix unitaire n'est pas récent, il est affiché sur un fond orange, vert sinon.

Voici comment on y arrive.

La source du contrôle qui fait office de fond (txtPrixActu) :

 

Sélectionnez

=(Date()-nz(RechDom("DateDernAchat";"tblIngredients";"IngredientPK =" & [cboIngredientFK]);DelaiPrixActu()+1))<DelaiPrixActu()

La valeur du contrôle est donc -1 si le prix est toujours d'actualité, 0 sinon. Cette valeur sert de base au formatage conditionnel.

II-A-2. Quelques explications « Métier »▲

Public Function PVN(PR As Single, idCategorie As Long) As Single
  Dim PCoeff As Single
  Dim PMin As Single
  Select Case idCategorie
    Case 1 'Entrées
      PCoeff = PR * CoeffEntr
      PMin = MinEntr
    Case 2 'Plats
      PCoeff = PR * CoeffPlat
      PMin = MinPlat
    Case 3 'Desserts
      PCoeff = PR * CoeffDess
      PMin = MinDess
  End Select
  'choix du plus avantageux
  If PCoeff > PMin Then
      PVN = PCoeff * (1 + TVANour)
    Else
      PVN = PMin
  End If
End Function

À l'instar des images dynamiques des plats, les images fixes du formulaire sont aussi à l'extérieur de la DB. (Pour la technique, voir : Stockez les images statiques de vos formulaires et états Access hors de la base de données.)

Les contrôles affichés en vert représentent des critères à « recherche stricte » : le choix du critère est limité aux items de la liste déroulante.

Les contrôles affichés en orange représentent des critères à « recherche élargie » : l'utilisateur peut faire une recherche en utilisant les items proposés par la liste ou sur la base d'une chaine de caractères de son choix.

L'utilisateur peut évidemment combiner les critères.

Par exemple : rechercher les recettes

- qui ont « asperge » dans la dénomination ;

- en Entrée ;

- actuellement à la carte ;

- qui contiennent « saumon » dans le nom d'un ingrédient.

La source du formulaire correspond à cette requête :

C'est-à-dire que nous prenons :

toutes les colonnes de la table « tblPlats » ;

dans l'ordre alphabétique croissant des dénominations,

mais les lignes de la table seront limitées à celles contenues dans la requête « rFiltresPlat ».

Cette requête source reste modifiable, c'est dire que dans le formulaire, on pourra ajouter, supprimer ou modifier le contenu de la table tblPlats.

Cette astuce permet d'avoir un formulaire qui combine les fonctionnalités traditionnelles (Ajouter, Supprimer, Modifier) avec celles d'un formulaire de recherche multicritère.

II-C-1. La requête rFiltrePlat▲

N.B. Pour pouvoir être incluse dans une fonction « In() », il est important que cette requête ne ramène qu'une seule colonne : les IdPlat.

Principe : on prend comme source la table que l'on aurait choisie « naturellement » comme source du formulaire (ici tblPlats) et on y joint toutes les tables du modèle qui sont nécessaires pour afficher les données sur lesquelles on veut filtrer.

Dans le cas présent : la dénomination, la catégorie (en clair), le statut (en clair) et l'ingrédient.

Comme une occurrence de IdPlat suffit, on regroupe sur idPlat.

Examinons les valeurs de la ligne « Critères » de la requête : elles ont toutes la même structure :

C'est-à-dire qu'on sélectionne les enregistrements qui contiennent :

- soit le texte exact (cas d'une sélection stricte) ;

- soit la chaine de caractères (cas d'une sélection élargie) ;

- soit tous les enregistrements, si le filtre est Null (non-choix).

II-C-2. Conclusion▲

Il y a deux points névralgiques : le prix des ingrédients et les quantités.
Nous devons réfléchir sur deux axes :
- comment organiser la mise à jour des prix unitaires ;
- comment contrôler la pertinence des quantités renseignées dans les fiches des recettes.

Tout n'a pas la même importance. Si dans la fiche de la recette on s'est trompé dans la quantité de sel ou qu'on a valorisé la farine au prix qu'elle coûtait il y a six mois, ça n'a guère de conséquences. Par contre, renseigner 125 g de filets de sole alors qu'en réalité on en met 150 sur l'assiette et si en plus le P.R. est basé sur les prix d'il y a un mois… il vaut mieux ne plus se servir de l'outil !

Voici le schéma de réflexion pour la suite :

• Sur la base des factures d'achats de matières premières, on encode les prix et les quantités des ingrédients « à suivre ». On peut ainsi :
  mettre à jour les prix dans tblIngredients ;
  enregistrer une entrée dans le stock de cet ingrédient (tblInventaire).
• Sur la base des plats vendus, on peut calculer la consommation théorique de chaque ingrédient « à suivre ».

Anciens stocks + Entrées réelles - consommations théoriques = la quantité qui devrait rester.

• On compare alors l'inventaire physique avec l'inventaire théorique et on se pose les bonnes questions en cas d'écart significatif :
  - « cela vient-il d'une erreur dans la quantité renseignée à la fiche Recette ? » On corrige celle-ci s'il échet ;
  - « commet-on des erreurs en cuisine, plats ratés à jeter ? » ;
  - « cela vient-il de matières périssables que l'on a dû jeter ? » Si oui, c'est intéressant d'en garder une preuve pour justifier une perte vis-à-vis d'un contrôle fiscal éventuel ;
  - « cela vient-il de matières qui disparaissent ? » Auquel cas, il faut resserrer des boulons !
  
  

Ce formulaire est aussi conçu sous le mode polyvalent : il permet non seulement l'ajout, la suppression et la modification d'enregistrements, mais aussi la recherche sur la base du fournisseur (recherche stricte) et de l'ingrédient (recherche élargie).

La mise en place est ici plus simple que celle expliquée pour le formulaire F_Recettes : pas besoin de recourir à une requête filtre : on peut filtrer directement dans la source et celle-ci restera modifiable.

Voici la source :

SELECT tblAchats.*
FROM tblIngredients INNER JOIN tblAchats ON tblIngredients.IngredientPK = tblAchats.IngredientFK
WHERE (((tblAchats.FournisseurFK) Like "*" & [forms]![F_Achats]![FiltreFournisseur] & "*") AND ((tblIngredients.Ingredient) Like "*" & [forms]![F_Achats]![FiltreIngredient] & "*"));

Pour faciliter l'encodage en série des postes d'une facture, les données susceptibles d'être identiques sont mises par défaut pour l'éventuel ajout suivant.

Private Sub cboFournisseurFK_AfterUpdate()
  If Me.NewRecord Then Me.cboFournisseurFK.DefaultValue = Me.cboFournisseurFK
  DoCmd.GoToControl "txtDateAchat"
End Sub
            
Private Sub txtDateAchat_AfterUpdate()
  If Me.NewRecord Then Me.txtDateAchat.DefaultValue = "#" & Format(txtDateAchat, "mm/dd/yy") & "#"
  DoCmd.GoToControl "txtRefAchat"
End Sub
            
Private Sub txtRefAchat_AfterUpdate()
  If Me.NewRecord Then Me.txtRefAchat.DefaultValue = txtRefAchat
  DoCmd.GoToControl "cboIngredient"
  Me.cboIngredient.Dropdown
End Sub

Cette donnée fait référence au document qui sert de base à l'encodage (sans doute une facture). L'utilisateur saisit n'importe quelle valeur de son choix qui lui permettra de retrouver facilement la pièce. Par exemple, le N° d'inscription au facturier des achats.

Cette donnée servira pour le processus d'inventaire. Éventuellement négative pour une note de crédit (allumée en rouge si égale à 0).

Cette donnée servira à la mise à jour du prix dans tblIngredient. Nécessairement supérieure à 0 (allumée en rouge sinon).

Calculé et affiché pour permettre un contrôle visuel du montant facturé.

C'est cet événement qui va provoquer la mise à jour du prix dans la table tblIngredients.

Le principe : à la fermeture du formulaire, on reporte dans tblIngredients, systématiquement le prix de l'achat le plus récent pour tous les ingrédients mentionnés dans la table tblAchats.

Cela signifie que non seulement les achats que l'on vient d'ajouter sont pris en compte, mais aussi toutes les modifications que l'utilisateur aurait éventuellement apportées dans des enregistrements anciens (pour corriger une erreur par exemple). Voir cependant la remarque au point II.C.

Voici le code :

Private Sub Form_Close()
            
  Dim strIngredients As String, strAchats As String
  Dim rsAchats As DAO.Recordset
            
  'Mettre à jour le prix des ingrédients
  DoCmd.SetWarnings False
  'Mettre les DateDernAchat à Null
  DoCmd.RunSQL "UPDATE tblIngredients INNER JOIN tblAchats ON tblIngredients.IngredientPK = tblAchats.IngredientFK " _
                 & "SET tblIngredients.DateDernAchat = Null;"
            
 'Ouvrir un Recordset sur la table tblAchats
 'qui extrait la dernière date et le prix correspondant de chaque ingrédient
            
    strAchats = "SELECT tblAchats.IngredientFK, tblAchats.PrixAchat, tblAchats.DateAchat " _
            & "FROM tblAchats,(SELECT tblAchats.IngredientFK, " _
            & "MAX(tblAchats.DateAchat) As DernDate FROM tblAchats GROUP BY tblAchats.IngredientFK) AS DatesRecentes " _
            & "WHERE tblAchats.IngredientFK = DatesRecentes.IngredientFK And tblAchats.DateAchat = DatesRecentes.DernDate "
            
  Set rsAchats = CurrentDb.OpenRecordset(strAchats, dbOpenDynaset)
            
 'Vérifie si le Recordset contient des enregistrements
 'et positionne le curseur sur le premier enregistrement du Recordset
  If Not rsAchats.EOF Then
    rsAchats.MoveFirst
  End If
            
 'Parcourt tous les enregistrements du Recordset
 'tant qu'on n'a pas atteint la fin
  Do While Not rsAchats.EOF
            
    'Requête MAJ qui permet la mise des colonnes Prix et DateDernAchat de la table T_Ingredients
    strIngredients = "UPDATE tblIngredients SET " _
                   & "tblIngredients.Prix = " & Replace(rsAchats!PrixAchat, ",", ".") _
                   & ", tblIngredients.DateDernAchat=#" & Format(rsAchats!DateAchat, "mm/dd/yy") & "# " _
                   & "WHERE tblIngredients.IngredientPK= " & rsAchats!IngredientFK _
                   & " And ((tblIngredients.DateDernAchat Is Null) Or " _
                   & "(tblIngredients.DateDernAchat<#" & Format(rsAchats!DateAchat, "mm/dd/yy") & "#));"
            
            
    CurrentDb.Execute strIngredients
    rsAchats.MoveNext
            
  Loop
            
  'Fermeture du Recordset
  rsAchats.Close
  'Libération de la variable Recordset rsAchats
  Set rsAchats = Nothing
            
  DoCmd.SetWarnings True
            
End Sub

Commentaires au sujet du code :

Dans tblIngredients, on remet à Null DateDernAchat de tous les ingrédients présents dans tblAchats. Ceci pour pouvoir recommencer la mise à jour du prix depuis l'origine.

Le code correspond à cette requête :

On crée une requête qui va ramener, pour chaque ingrédient, le prix de l'achat le plus récent :

On crée un recordset qui contient les enregistrements ramenés par cette requête et pour chaque item, on opère la mise à jour du Prix et DernDate dans tblIngredients en exécutant la requête décrite au point suivant.

Voici la représentation graphique de la requête exécutée pour chaque dernier prix de chaque ingrédient (ici pour l'ingrédient 21, au 22 juin 2013) :

Si à la suite d'une modification, il n'y a plus d'enregistrement dans tblAchats pour cet ingrédient, ni son prix, ni sa date ne seront modifiés dans tblIngrédients (la mise à Null de DateDernAchat ne concerne que les ingrédients présents dans tblAchats).

Pour éviter cet écueil, on va remettre DateDernAchat à Null et Prix à zéro dès que l'on détecte une modification de l'ingrédient ou la suppression d'un enregistrement. Voici le code :

Option Compare Database
Option Explicit
            
  Dim iIngredientIni As Integer
Private Sub Form_Current()
  'Mémoriser l'ingrédient initial
  If Not Me.NewRecord Then iIngredientIni = Me.cboIngredient
End Sub
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
  Dim sSql As String
  'Modifier la DateDernAchat de l'ingrédient concerné dans tblIngredients
    sSql = "UPDATE tblIngredients SET DateDernAchat = Null, tblIngredients.Prix = 0 WHERE tblIngredients.IngredientPK=" & iIngredientIni & ";"
  DoCmd.SetWarnings False
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
End Sub
            
Private Sub cboIngredient_BeforeUpdate(Cancel As Integer)
  Dim sSql As String
  If Me.NewRecord Then Exit Sub
  'Modifier la DateDernAchat de l'ancien ingrédient concerné dans tblIngredients
    sSql = "UPDATE tblIngredients SET DateDernAchat = Null, tblIngredients.Prix = 0 WHERE tblIngredients.IngredientPK=" & Me.cboIngredient.OldValue & ";"
  DoCmd.SetWarnings False
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
End Sub

VI-B-1. À l'ouverture le formulaire…▲

… affiche les données du dernier inventaire qui a été enregistré, c'est-à-dire les enregistrements qui ont la date la plus récente dans tblInventaire.

Les éventuels nouveaux ingrédients sont ajoutés :

 

Sélectionnez

Private Sub Form_Open(Cancel As Integer)
  Dim sSql As String
  Dim dDernInven As Date
  dDernInven = DMax("InvDate", "tblInventaire")
  Me.TxtDateDepart = dDernInven + 1
  Me.etDernier.Caption = "Dernier inventaire " & dDernInven
  'Ajouter les nouveaux ingrédients éventuels
  DoCmd.SetWarnings False
  sSql = "INSERT INTO tblInventaire ( IngredientFK, InvDate ) " _
       & "SELECT IngredientPK, #" & Format(dDernInven, "mm/dd/yy") & "# AS Expr1 " _
       & "FROM tblIngredients GROUP BY IngredientPK;"
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
  'Afficher les stocks du dernier inventaire
  Me.Filter = "InvDate = #" & Format(dDernInven, "mm/dd/yy") & "#"
  Me.FilterOn = True
End Sub

VI-B-2. L'utilisateur complète la date du nouvel inventaire…▲

D'autres données s'affichent alors :

Le bouton a changé de légende et devient « Activé ».

On affiche la somme des achats de chaque ingrédient pendant la période qui nous sépare du précédent inventaire. On exploite pour cela la requête rInventaireDetailAchats que nous avons évoquée plus haut.

On affiche la somme des consommations théoriques de chaque ingrédient pendant la période qui nous sépare du précédent inventaire. On exploite pour cela la requête rInventaireDetailConsom que nous avons évoquée plus haut.

VI-B-3. L'utilisateur complète alors la colonne « Stock »…▲

avec les données qu'il constate sur place actuellement :

Il examine et essaie d'expliquer les écarts.

Un double-clic sur le montant d'un achat en affiche le détail :

 

Sélectionnez

Private Sub TxtEntrees_DblClick(Cancel As Integer)
  'Formater le formulaire F_InventaireDetail
  If CurrentProject.AllForms("F_InventaireDetail").IsLoaded Then DoCmd.Close acForm, "F_InventaireDetail"
  DoCmd.OpenForm "F_InventaireDetail", , , , , acHidden
  Forms!F_InventaireDetail.RecordSource = "SELECT * FROM rInventaireDetailAchats " _
                                         & "WHERE IngredientFK=" & Me.txtIngredientFK & ";"
  Forms!F_InventaireDetail!Titre.Caption = "Détail des achats de " & Me.TxtEntrees _
                               & " " & Forms!F_InventaireDetail!txtLibelleUV & Chr(13) & Chr(10) _
                               & Me.txtIngredient & Chr(13) & Chr(10) _
                               & "entre le " & Me.TxtDateDepart _
                               & " et le " & Me.TxtDateFin
  'Positionner le formulaire F_InventaireDetail
  Call PositionFormBis("F_InventaireDetail", Me.ActiveControl)
End Sub

De la même manière, un double-clic sur le total des consommations en affiche le détail :

 

Sélectionnez

Private Sub txtConsom_DblClick(Cancel As Integer)
  'Formater le formulaire F_InventaireDetail
  If CurrentProject.AllForms("F_InventaireDetail").IsLoaded Then DoCmd.Close acForm, "F_InventaireDetail"
  DoCmd.OpenForm "F_InventaireDetail", , , , , acHidden
  Forms!F_InventaireDetail.RecordSource = "SELECT * FROM rInventaireDetailConsom " _
                                        & "WHERE IngredientPK=" & Me.txtIngredientFK & ";"
  Forms!F_InventaireDetail!Titre.Caption = "Détail des consommations de " & Format(Me.txtConsom, "#,#0.00") _
                               & " " & Forms!F_InventaireDetail!txtLibelleUV & Chr(13) & Chr(10) _
                               & Me.txtIngredient & Chr(13) & Chr(10) _
                               & "entre le " & Me.TxtDateDepart _
                               & " et le " & Me.TxtDateFin
  'Positionner le formulaire F_InventaireDetail
  Call PositionFormBis("F_InventaireDetail", Me.ActiveControl)
End Sub

Dans un cas comme dans l'autre, le formulaire qui affiche le détail se positionne immédiatement en dessous du contrôle que l'utilisateur a double-cliqué (au-dessus à défaut de place suffisante à l'écran).

Pour ce faire, on utilise une procédure mise au point par Arkham46, dans cette contribution.

VI-B-4. Quand l'examen est terminé▲

Le clic sur ce bouton provoque la création des enregistrements dans tblInventaire. Et on est reparti pour une nouvelle période de travail avant le prochain inventaire…

 

Sélectionnez

Private Sub btCreerInvSuivant_Click()
  Dim sSql As String
  Me.Refresh
  DoCmd.SetWarnings False
  'Purge d'une création antérieure éventuelle
  DoCmd.RunSQL "DELETE * FROM tblInventaire  WHERE InvDate>#" & Format(Me.TxtDateDepart, "mm/dd/yy") & "#;"
  'MàJ tblInventaire pour enregistrer le nouvel inventaire
  sSql = "INSERT INTO tblInventaire ( Stock, IngredientFK, InvDate ) " _
       & "SELECT InvSuivant, IngredientFK, #" & Format(Me.TxtDateFin, "mm/dd/yy") & "# AS Expr1 " _
       & "FROM tblInventaire " _
       & "WHERE InvDate=#" & Format(Me.TxtDateDepart - 1, "mm/dd/yy") & "#;"
  DoCmd.RunSQL sSql
  DoCmd.SetWarnings True
End Sub