Livrer un rapprochement des ventes

Introduction

PAPER & PIER a besoin d’un dossier de clôture : une ligne CSV par commande et un récapitulatif pour chaque stand. Les deux stands peuvent présenter un écart global nul alors que certaines commandes individuelles restent insuffisamment ou trop payées. Le dossier doit conserver ces exceptions au lieu de conclure à la réussite sur la base d’un seul total général.

Vous enregistrerez une requête SQL réutilisable et utiliserez l’outil d’export fourni pour créer de véritables fichiers CSV à partir de ses résultats réels. Terminez d’abord Conserver un total correct pour chaque commande, ou maîtrisez SUM/GROUP BY, LEFT JOIN, COALESCE et le problème de duplication par jointure. Les expressions de table communes et la classification des états sont expliquées ici. Utilisez Terminal tout au long du TP ; cette unité possède son propre registre indépendant de six commandes.

Conservez les quatre tables sources et les outils fournis sans les modifier. Modifiez uniquement query.sql et les fichiers de reports/ ; n’ajoutez pas d’objets à la base. Tous les montants sont des centimes entiers. Il s’agit d’un rapprochement simplifié des encaissements, sans taxes ni règlement par un prestataire de paiement. L’outil d’export gère l’encodage CSV et les calculs récapitulatifs ; vous n’avez pas besoin d’écrire du Python.

Enregistrer une requête réutilisable par commande

Dans cette étape, vous calculerez les encaissements nets, l’écart et l’état de chaque commande à l’aide d’une seule instruction SQL autonome. Commencez dans Terminal :

cd /home/labex/project/sales-desk

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

Ce registre indépendant contient A1 à A6, dont deux commandes libellées Alex Kim et des libellés contenant des virgules ou des guillemets. Les identifiants distinguent les commandes ; conservez les libellés exactement tels qu’ils sont enregistrés. Examinez chaque liste de faits avant de calculer les soldes :

sqlite3 -header -column ledger.db "SELECT * FROM items ORDER BY item_id; SELECT * FROM payments ORDER BY payment_id; SELECT * FROM refunds ORDER BY refund_id;"

Comme précédemment, seuls les paiements et remboursements succeeded comptent. Deux événements réussis de même montant comptent tous les deux. Les faits admissibles manquants contribuent pour zéro. Les calculs sont les suivants :

Une expression de table commune (CTE) est un SELECT intermédiaire nommé, disponible uniquement à l’intérieur d’une instruction. WITH name AS (...) l’introduit ; des virgules séparent plusieurs CTE. Elle permet le même raisonnement par faits séparés que les vues précédentes, sans créer d’objets de base de données ni dépendre des fichiers d’un autre TP.

D’abord, trois CTE agrègent chaque fait indépendamment. Ensuite, totals part des commandes et remplace les totaux manquants par zéro. balances calcule le net et l’écart à partir de ces totaux nommés. Le dernier SELECT utilise CASE WHEN ... THEN ... ELSE ... END pour choisir l’état selon le signe de l’écart. Ces couches rendent les alias disponibles pour le SELECT suivant au lieu de dépendre de l’alias d’une autre expression dans la même liste SELECT. SELECT * dans la couche intermédiaire de calcul des soldes transmet les totaux ; le SELECT final énumère explicitement exactement neuf colonnes dans l’ordre requis.

Enregistrez l’instruction complète. cat > remplace le fichier ; le here-document entre guillemets conserve le SQL littéralement. Incluez la ligne finale SQL :

cat > query.sql <<'SQL'
WITH
item_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS due_cents
  FROM items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'succeeded' GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount_cents) AS refunded_cents
  FROM refunds WHERE status = 'succeeded' GROUP BY order_id
),
totals AS (
  SELECT o.order_id, o.customer, o.stall,
         COALESCE(i.due_cents, 0) AS due_cents,
         COALESCE(p.paid_cents, 0) AS paid_cents,
         COALESCE(r.refunded_cents, 0) AS refunded_cents
  FROM orders AS o
  LEFT JOIN item_totals AS i ON i.order_id = o.order_id
  LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
  LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
),
balances AS (
  SELECT *, paid_cents - refunded_cents AS net_cents,
         paid_cents - refunded_cents - due_cents AS difference_cents
  FROM totals
)
SELECT order_id, customer, stall, due_cents, paid_cents,
       refunded_cents, net_cents, difference_cents,
       CASE WHEN difference_cents = 0 THEN 'settled'
            WHEN difference_cents < 0 THEN 'underpaid'
            ELSE 'overpaid' END AS status
FROM balances
ORDER BY order_id;
SQL

N’incluez pas de commandes SQLite commençant par un point ni de commandes shell dans query.sql : l’outil d’export exécute une seule instruction SELECT/WITH ordinaire. Elle doit fonctionner à partir des quatre tables sources sur un autre registre de même schéma, y compris avec des identifiants de commande et des montants différents. Des lignes de résultat codées en dur ou des vues de base de données ne constitueraient pas cette requête réutilisable.

< envoie ce fichier à la CLI SQLite pour une première exécution lisible :

sqlite3 -header -column ledger.db < query.sql

Lisez les véritables lignes, pas seulement un total. Les soldes attendus sont les suivants :

A2 reste visible sans paiement. A3 conserve les deux remboursements réussis de 50 centimes. Le remboursement échoué d’A6 ne réduit pas ses encaissements nets. A1 et A2 restent distinctes malgré leur libellé client commun. Les cinq colonnes en centimes sont de véritables entiers, y compris les écarts négatifs ; ne les transformez pas en chaînes monétaires formatées.

En cas d’erreur de syntaxe, lisez l’emplacement signalé et remplacez query.sql par le bloc complet corrigé avant de relancer. En cas de mauvais chemin, revenez avec le cd absolu ci-dessus et utilisez ls pour trouver query.sql. Ne videz pas le registre, ne modifiez pas les montants pour forcer l’équilibre et n’ajoutez pas de tables de résultats enregistrés.

Livrer et lire les véritables fichiers CSV

Dans cette étape, vous exporterez la requête enregistrée et lirez ses véritables fichiers de clôture, y compris les libellés qui nécessitent des guillemets CSV. Commencez dans Terminal, dans /home/labex/project/sales-desk.

./export est un exécutable fourni dans le répertoire courant. Il lit query.sql, exécute cette instruction inchangée sur ledger.db via une connexion en lecture seule et écrit les véritables lignes renvoyées dans reports/orders.csv. Il dérive reports/summary.csv uniquement de ces lignes renvoyées. Il ne corrige pas une mauvaise requête et n’ajoute pas les commandes manquantes. Le répertoire préparé reports est l’emplacement des deux fichiers :

./export

Le message indique six lignes de requête et nomme les deux fichiers de sortie. Le lecteur ci-dessous affichera deux récapitulatifs de stands. Un export réussi signifie que les fichiers ont été écrits, pas que vos calculs sont corrects. Si la requête manque ou est invalide, l’outil d’export signale un échec. D’anciens fichiers peuvent encore être présents après un export réussi antérieur : corrigez la requête enregistrée et relancez ./export avant de les considérer comme actuels.

./review est le lecteur CSV fourni. Il lit les fichiers enregistrés sans consulter la base et affiche chaque ligne analysée comme un enregistrement avec des champs nommés. Une sortie manquante ou modifiée reste manquante ou modifiée :

./review

Les six enregistrements de commande doivent correspondre au tableau de l’étape précédente, avec les libellés client et stand exactement d’origine. Les colonnes récapitulatives sont les suivantes :

North, Pier possède trois commandes : dû 1650, net 1650, écart 0, et une commande de chaque état. South en possède également trois : dû 1100, net 1100, écart 0, et une commande de chaque état. Sur les deux stands, quatre commandes ne sont pas réglées exactement, même si l’écart général est nul. Les trop-perçus et les insuffisances se compensent dans un total ; ils ne règlent pas la commande de quelqu’un d’autre.

CSV signifie « valeurs séparées par des virgules », mais une virgule dans un libellé fait partie d’un seul champ. Le CSV standard entoure ces champs de guillemets doubles et représente un guillemet dans un champ par deux caractères de guillemet. cat affiche les octets bruts du fichier sous forme de texte ; comparez cet encodage aux libellés correctement analysés par ./review :

cat reports/orders.csv

cat reports/summary.csv

Dans le fichier des commandes, le libellé client Paper, "Pine" est encodé sous la forme "Paper, ""Pine""" ; North, Pier est encodé sous la forme "North, Pier". Ces guillemets supplémentaires appartiennent à la syntaxe CSV et ne modifient pas les libellés. Découper chaque ligne à chaque virgule corromprait ces enregistrements ; les outils d’écriture et de lecture fournis gèrent le format.

Si un CSV manque, relancez ./export après avoir corrigé query.sql, puis relisez les deux fichiers. Ne modifiez pas manuellement un récapitulatif pour masquer des commandes manquantes, ne remplacez pas l’outil d’export ou le lecteur et ne modifiez pas les enregistrements sources. Votre dossier de clôture comprend le query.sql réutilisable et les deux véritables fichiers CSV. Les fichiers doivent contenir une ligne pour chaque commande source, les neuf colonnes de commande exactes et les huit colonnes récapitulatives présentées ci-dessus. L’ordre des lignes n’est pas le résultat métier ; ce sont les identités, valeurs, libellés et leurs relations qui comptent.

Résumé

Vous avez construit une requête CTE réutilisable, classé chaque commande à partir de ses encaissements nets moins la valeur de ses articles et livré deux véritables fichiers CSV. Vous avez lu des libellés correctement entourés de guillemets et distingué les totaux équilibrés des stands des commandes individuellement réglées.