Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

MS SQL Server Discussion :

Numéro de facture unique et séquentiel


Sujet :

MS SQL Server

  1. #1
    Membre à l'essai
    Numéro de facture unique et séquentiel
    Bonjour,

    Dans le cadre d'un progiciel, nous souhaiterions mettre en place un système de génération de code facture dont le format peut différer dans l'absolu en fonction des demandes clients .

    Notre code facture pourrait donc avoir un format différent : (Exemple 1 : 20190000001 ; Exemple 2 : 1900001)

    Nous pensions à une procédure stockée qui pourrait être personnalisée avec sélection et maj d'une table de chrono (Cette procédure serait ensuite encapsulée dans une transaction explicite permettant ainsi d'annuler l'attribution du chrono si l'insertion de la facture échoue)

    Ce système pourrait ensuite être généralisé à d'autres métiers comme le code client, code achat, etc...

    Table Chrono envisagée :
    Ch_table Ch_prefixe Ch_prochain
    Facture 2019 0000002
    Facture 19 00002
    Client 195 000001
    Achat A19 000000018

    Que pensez-vous de la piste évoquée, merci par avance.

  2. #2
    Expert éminent
    Pourquoi pas, tant que ce n'est pas utilisé comme pk / fk des tables.
    les règles du forum - mode d'emploi du forum
    Aucun navigateur ne propose d'extension boule-de-cristal : postez votre code et vos messages d'erreurs. (Rappel : "ça ne marche pas" n'est pas un message d'erreur)
    JE NE RÉPONDS PAS aux questions techniques par message privé.

  3. #3
    Expert éminent sénior
    J'abonde dans le sens de la réponse qui précède formulée par heu... 7gyY9w1ZY6ySRgPeaefZ (merci le copier/coller )

    Cet identifiant, même s'il est unique, ne doit surtout pas devenir la clef primaire dans la table FACTURE (ni de ce fait contribuer à la constitution de la PK de la table ligne facture)

  4. #4
    Membre expert
    Citation Envoyé par mbsl1 Voir le message
    Nous pensions à une procédure stockée qui pourrait être personnalisée avec sélection et maj d'une table de chrono (Cette procédure serait ensuite encapsulée dans une transaction explicite permettant ainsi d'annuler l'attribution du chrono si l'insertion de la facture échoue)
    Vous pouvez aussi NE numéroter les factures QUE lorsque leur insertion a réussi...

  5. #5
    Membre éclairé

    J'abonde dans le sens de la réponse qui précède formulée par heu... 7gyY9w1ZY6ySRgPeaefZ (merci le copier/coller )

    Cet identifiant, même s'il est unique, ne doit surtout pas devenir la clef primaire dans la table FACTURE (ni de ce fait contribuer à la constitution de la PK de la table ligne facture)
    J'abonde aussi dans ce sens. la clé primaire doit être un incrémental(identity column)

    Cordialement,

    Bernardos
    Loïc BERNARD
    Consultant Senior dba sql server & Microsoft Business Intelligence



    Il n'y a jamais de problèmes, il n'y a que des solutions!

  6. #6
    Membre du Club
    Bonjour !

    J'ai eu le même problème. Ceci peut être de quelque utilité...

    Le "numéro" de Facture est un string, et il est complètement indépendant de la clef primaire.

    L'appli dessert une ou plusieurs entreprises (YrVetStructureID), et un ou plusieurs sites.

    Voilà d'abord le code qui a fait ses preuves pour les Bons de livraison. Les BLs sont identifiés Année + Mois + Tag de l'ordi + Increment de 1 à 99999 (on peut varier en Options !). Il est dès lors très facile de filtrer et de trier les BLs pour savoir qui a fait quoi, et quand.

    Le BLNr est naturellement mis à jour après la création du nouvel enregistrement (DataEntry).

    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    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
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    Function modGetNewBillNumber(YrVetStructureID As Long) As String
     
    'Create a new Bill Number
     
    Dim strCrtYear As String, strCrtMonth As String, CrtComptrNameST As String, CrtComptCapsTag As String, _
        strLastBillIncr As String, lngLastBillIncr As Long, _
        lngNewBillIncr As Long, CntOfZero As Long
     
    '1. Get string for current Year and current Month
            strCrtYear = CStr(Year(Now))
            If Len(CStr(Month(Now))) = 1 Then
                strCrtMonth = "0" & CStr(Month(Now))
            ElseIf Len(CStr(Month(Now))) = 2 Then
                strCrtMonth = CStr(Month(Now))
            End If
     
    '2. Get current computer capital letters Tag (PosteAbr in Table "VetStructuresPostes")
            CrtComptrNameST = Environ("Computername")
            CrtComptCapsTag = DFirst("PosteAbr", "VetStructuresPostes", "ComputerName = '" & CrtComptrNameST & "' And VetStructureID =" & YrVetStructureID & "")
     
    '3. Get last Bill increment issued by this computer, both as string and as long
            If DCount("BLID", "BL") = 0 Then
                strLastBillIncr = "0000000000000"
                lngLastBillIncr = 0
            Else
                strLastBillIncr = Nz(DMax("Right([BLNr], 5)", "BL", "BLNr like '*" & CrtComptCapsTag & "*' And VetStructureID =" & YrVetStructureID & ""), 0)
                lngLastBillIncr = Nz(DMax("Right([BLNr], 5)", "BL", "BLNr like '*" & CrtComptCapsTag & "*' And VetStructureID =" & YrVetStructureID & ""), 0)
            End If
     
    '4. Get new Bill increment
            If lngLastBillIncr < 100000 Then
                lngNewBillIncr = Nz(lngLastBillIncr) + 1
            Else
                lngNewBillIncr = 1
            End If
     
    '5. Concatenate Year + Month + Tag + Increment
            CntOfZero = 5 - Len(CStr(lngNewBillIncr))
     
            If CntOfZero = 4 Then
                modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "0000" & CStr(lngNewBillIncr)
            ElseIf CntOfZero = 3 Then
                modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "000" & CStr(lngNewBillIncr)
            ElseIf CntOfZero = 2 Then
                modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "00" & CStr(lngNewBillIncr)
            ElseIf CntOfZero = 1 Then
                modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & "0" & CStr(lngNewBillIncr)
            ElseIf CntOfZero = 0 Then
                modGetNewBillNumber = strCrtYear & strCrtMonth & CrtComptCapsTag & CStr(lngNewBillIncr)
            End If
     
    End Function


    Même principe pour les Factures (modGetNewInvoiceNumber).
    Se souvenir que la loi française impose une série de numéros unique et continue, mais qu'elle autorise aussi des séries distinctes dans certaines circonstances. L'appli donne donc le choix aux Admins de prendre (ou non) une telle liberté, en fonction de leurs besoins et de la règlementation fiscale de leur pays : le Tag de l'ordi est optionnel pour les factures.
    (pour les BLs ça n'a aucune importance).

    N'oubliez pas de créer ensuite un petit compilateur pour toujours bien détecter l'absence de trous et l'absence de doublons ! Me.Form.AllowDeletions = False, etc, etc,... Bien veiller aussi aux problèmes de synchronisation entre postes !

    Bon courage !

    phil

  7. #7
    Rédacteur

    Quelques éléments d'information important :
    1) Ce qui est vu dans une facture comme numéro, n'est pas forcément ce qui est stocké dans la base. C'est pour cela que l'on a formé la notion de vue.
    2) mélanger dans une même colonne de table différentes informations (années + numéro relatif à l'année) viole les principes de modélisation et en particulier la forme normale n°1 (1FN), ce qui rend caduc toute construction d'un modèle relationnel conforme et efficace. Les deux notions doivent êtres stockées séparément (date par exemple d'un côté et n° autoincrémenté de l'autre) ce qui conduira à respecter la 1FN et par conséquent, possiblement toutes les autres formes normales. La vue pouvant présenter la concaténation de ces deux éléments d'information (normalement on ne devrait jamais attaquer un Bbase de données relationnelles directement par les tables, mais toujours passer par les vues....)
    3) Quelque soit la méthode d'auto incrémentation, il est toujours possible de forcer une valeur ou refaire repartir le compteur d'autoincrémentation. Par conséquent, l'auto incrémentation ne garantie en aucune cas l'unicité. Il faut ajouter une contrainte d'unicité.
    4) Le n° attribué est "grillé" en cas d'annulation de la transaction (ROLLBACK). Par exemple si vous créez une nouvelle facture portant le n°17 et que vous vous appercevez que cette facture n'est ps bonne et que vous décidez de la supprimer, la facture suivante portera le n°18 parce qu'il n'y a pas de reprise d'un numéro préalablement attribué, même en cas de suppression de la facture 17
    5) Dans la réalité, hors de l'informatique, la situation est la même. Un document appelé facturier (manifold) est composé de feuilles (généralement en triplicata) numérotées successivement, (par exemple de 1 à 100) sur lequel on édite les factures. En cas d'erreur, on raye les éléments inscrite avec un barré en travers en inscrivant la mention "annulée pour cause d'erreur". C'est exactement ce qu'il faut faire dans votre application : laisser les factures, même annulée et prévoir un statut "annulée".
    Exemple : https://www.papeterie-gouchon.com/up...1455031518.jpg

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  8. #8
    Rédacteur

    Exemple en SQL :

    La table :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    CREATE TABLE T_FACTURE_FAC
    (FAC_ID                       INT IDENTITY NOT NULL PRIMARY KEY,
     FAC_DATE                     DATE NOT NULL DEFAULT GETDATE(),
     FAC_ANNEE                    AS YEAR(FAC_DATE) PERSISTED NOT NULL
        CONSTRAINT UK_FAC_ID_DATE UNIQUE (FAC_ID, FAC_ANNEE)),
     CLI_ID                       INT REFERENCES T_CLIENT_CLI (CLI_ID),
     ...
     )



    La vue :
    Code :Sélectionner tout -Visualiser dans une fenêtre à part
    1
    2
    3
    4
    CREATE VIEW V_FACTURE_FAC
    AS
    SELECT CAST(FAC_ANNEE AS CHAR(4)) + FORMAT(FAC_ID, '000000') AS FAC_NUM, *
    FROM   T_FACTURE_FAC


    Pour rappel vous pouvez faire des INSERT, UPDATE et DELETE dans une vue....

    A +
    Cette signature n'a pas pu être affichée car elle comporte des erreurs.

  9. #9
    Membre du Club
    Un très grand merci pour cet éclairage, Frédéric ! Super sympa un jour de Pentecote !


    1) Ce qui est vu dans une facture comme numéro, n'est pas forcément ce qui est stocké dans la base. C'est pour cela que l'on a formé la notion de vue.
    Tout-à-fait !

    2) mélanger dans une même colonne de table différentes informations (années + numéro relatif à l'année) viole les principes de modélisation et en particulier la forme normale n°1 (1FN), ce qui rend caduc toute construction d'un modèle relationnel conforme et efficace. Les deux notions doivent êtres stockées séparément (date par exemple d'un côté et n° autoincrémenté de l'autre) ce qui conduira à respecter la 1FN et par conséquent, possiblement toutes les autres formes normales. La vue pouvant présenter la concaténation de ces deux éléments d'information (normalement on ne devrait jamais attaquer un Bbase de données relationnelles directement par les tables, mais toujours passer par les vues....)
    La colonne InvoiceNr est un simple champ-texte, un pense-bête pour l'utilisateur, qui n'intervient dans aucune relation. Les colonnes importantes sont InvoiceID (clé-primaire) et InvoiceDate. Pour le modèle "à plat" que demande le fisc, l'incrément max doit être égal au nombre de lignes de la Table.

    3) Quelque soit la méthode d'auto incrémentation, il est toujours possible de forcer une valeur ou refaire repartir le compteur d'autoincrémentation. Par conséquent, l'auto incrémentation ne garantie en aucune cas l'unicité. Il faut ajouter une contrainte d'unicité.
    Tout-à-fait : "No Duplicates"

    4) Le n° attribué est "grillé" en cas d'annulation de la transaction (ROLLBACK). Par exemple si vous créez une nouvelle facture portant le n°17 et que vous vous appercevez que cette facture n'est ps bonne et que vous décidez de la supprimer, la facture suivante portera le n°18 parce qu'il n'y a pas de reprise d'un numéro préalablement attribué, même en cas de suppression de la facture 17
    La suppression d'une facture doit être rendue impossible ! Archivage PDF des originaux, pas de Kill possible... En cas d'erreur, la seule solution est d'effectuer un Avoir.

    Idéalement pour les avoirs, une appli doit pouvoir se référer aux lignes des BL's précédants, et/ou au total d'une facture annulée. Or beaucoup d'appli ne le font pas ! Je me suis résigné à le faire, car en pratique de clientèle, une secrétaire reprend souvent des produits inutilisés par un client, et elle crée alors un BL négatif sur base du tarif en cours qui peut avoir augmenté de 10% après 6 mois ! C'est ainsi qu'une entreprise peut perdre beaucoup d'argent... Sans compter la gestion du stock par numéros de lots et dates de validité !

    5) C'est exactement ce qu'il faut faire dans votre application : laisser les factures, même annulée et prévoir un statut "annulée".
    Parfaitement !




    Exemple : https://www.papeterie-gouchon.com/up...1455031518.jpg

###raw>template_hook.ano_emploi###