Entregar una conciliación de ventas

Introducción

PAPER & PIER necesita una entrega de cierre: una fila CSV por pedido y un resumen por puesto. Ambos puestos pueden tener una diferencia global de cero mientras algunos pedidos individuales siguen con pagos insuficientes o excesivos. La entrega debe conservar esas excepciones en lugar de anunciar que todo está correcto a partir de un único total general.

Guardarás una consulta SQL reutilizable y usarás el exportador proporcionado para crear archivos CSV reales a partir de sus resultados efectivos. Completa primero Mantener correcto el total de cada pedido, o conoce SUM/GROUP BY, LEFT JOIN, COALESCE y el problema de duplicación por uniones. Aquí se explican las expresiones de tabla comunes y la clasificación de estados. Usa Terminal durante todo el laboratorio; esta unidad tiene su propio libro de registros independiente de seis pedidos.

Mantén sin cambios las cuatro tablas fuente y las herramientas proporcionadas. Modifica solo query.sql y los archivos de reports/; no añadas objetos a la base de datos. Todo el dinero se expresa en centavos enteros. Esta es una conciliación simplificada de cobros, sin impuestos ni liquidación del proveedor de pagos. El exportador se encarga de la codificación CSV y de los cálculos del resumen; no necesitas escribir Python.

Guardar una consulta reutilizable por pedido

En este paso, calcularás los cobros netos, la diferencia y el estado de cada pedido mediante una única instrucción SQL autocontenida. Empieza en Terminal:

cd /home/labex/project/sales-desk

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

Este libro de registros independiente contiene de A1 a A6, incluidos dos pedidos etiquetados como Alex Kim y etiquetas que contienen comas o comillas. Los ID distinguen los pedidos; conserva las etiquetas exactamente como están almacenadas. Revisa todas las listas de datos antes de calcular los saldos:

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;"

Como antes, solo cuentan los pagos y reembolsos con estado succeeded. Dos eventos completados con importes iguales cuentan ambos. Si no hay datos que cumplan los criterios, su aportación es cero. Los cálculos son:

Una expresión de tabla común (CTE) es un SELECT intermedio con nombre disponible solo dentro de una instrucción. WITH name AS (...) la introduce; las comas separan varias CTE. Permite razonar sobre datos separados igual que las vistas anteriores, sin crear objetos en la base de datos ni depender de archivos de otro laboratorio.

Primero, tres CTE agregan cada conjunto de datos de forma independiente. Después, totals parte de los pedidos y rellena los totales ausentes con cero. balances calcula el neto y la diferencia a partir de esos totales con nombre. El último SELECT usa CASE WHEN ... THEN ... ELSE ... END para elegir el estado según el signo de la diferencia. Estas capas hacen que los alias estén disponibles para el SELECT siguiente, en lugar de depender del alias de otra expresión de la misma lista SELECT. SELECT * en la capa intermedia de saldos transmite los totales; el SELECT final enumera explícitamente exactamente nueve columnas en el orden requerido.

Guarda la instrucción completa. cat > reemplaza el archivo; el here-document entre comillas conserva el SQL literalmente. Incluye la línea final 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

No incluyas comandos de punto de SQLite ni comandos del intérprete del sistema en query.sql: el exportador ejecuta una única instrucción SELECT/WITH ordinaria. Debe funcionar a partir de las cuatro tablas fuente en otro libro de registros con el mismo esquema, incluidos ID de pedido e importes distintos. Las filas de resultados codificadas como valores fijos o las vistas de base de datos no proporcionarían esa consulta reutilizable.

< envía este archivo a la interfaz de línea de comandos de SQLite para una primera ejecución legible:

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

Lee las filas reales, no solo un total. Los saldos esperados son:

A2 permanece visible sin un pago. A3 conserva ambos reembolsos completados de 50 centavos. El reembolso fallido de A6 no reduce sus cobros netos. A1 y A2 siguen separados aunque compartan una etiqueta de cliente. Las cinco columnas de centavos contienen enteros reales, incluidas las diferencias negativas; no las conviertas en cadenas con formato monetario.

Ante un error de sintaxis, lee la ubicación indicada y reemplaza query.sql con el bloque completo corregido antes de volver a ejecutarlo. Ante una ruta incorrecta, vuelve usando el cd absoluto anterior y usa ls para encontrar query.sql. No vacíes el libro de registros, modifiques importes para forzar un equilibrio ni añadas tablas de resultados almacenados.

Entregar y leer los archivos CSV reales

En este paso, exportarás la consulta guardada y leerás sus archivos reales de entrega, incluidas las etiquetas que requieren comillas CSV. Empieza en Terminal, en /home/labex/project/sales-desk.

./export es un ejecutable proporcionado en el directorio actual. Lee query.sql, ejecuta esa instrucción sin cambios sobre ledger.db usando una conexión de solo lectura y escribe las filas realmente devueltas en reports/orders.csv. Deriva reports/summary.csv únicamente de esas filas devueltas. No corrige una consulta errónea ni añade pedidos que falten. El directorio preparado reports es donde deben estar ambos archivos:

./export

El mensaje informa de seis filas de consulta y nombra ambos archivos de salida. El lector siguiente mostrará dos resúmenes de puestos. Una exportación correcta significa que se escribieron archivos, no que tus cálculos sean correctos. Si falta la consulta o no es válida, el exportador informa de un fallo. Todavía puede haber archivos antiguos de una exportación anterior correcta: corrige la consulta guardada y vuelve a ejecutar ./export antes de considerarlos actuales.

./review es el lector CSV proporcionado. Lee los archivos guardados sin consultar la base de datos y muestra cada fila interpretada como un registro con etiquetas. Los resultados que falten o estén alterados siguen faltando o alterados:

./review

Los seis registros de pedido deberían coincidir con la tabla del paso anterior, con las etiquetas originales exactas de cliente y puesto. Las columnas del resumen son:

North, Pier tiene tres pedidos: importe debido 1650, neto 1650, diferencia 0 y uno de cada estado. South también tiene tres: importe debido 1100, neto 1100, diferencia 0 y uno de cada estado. Entre ambos puestos hay cuatro pedidos sin saldar aunque la diferencia general sea cero. Los pagos excesivos y los insuficientes se compensan en un total; no saldan el pedido de otra persona.

CSV significa valores separados por comas, pero una coma dentro de una etiqueta forma parte de un único campo. El CSV estándar rodea esos campos con comillas dobles y representa una comilla dentro de un campo mediante dos caracteres de comilla. cat muestra los bytes del archivo sin procesar como texto; compara esa codificación con las etiquetas correctamente interpretadas por ./review:

cat reports/orders.csv

cat reports/summary.csv

En el archivo de pedidos, la etiqueta de cliente Paper, "Pine" se codifica como "Paper, ""Pine"""; North, Pier se codifica como "North, Pier". Esas comillas adicionales son sintaxis CSV, no cambios en las etiquetas. Dividir cada línea en cada coma dañaría estos registros; el escritor y el lector proporcionados gestionan el formato.

Si falta un CSV, vuelve a ejecutar ./export después de corregir query.sql y después lee ambos archivos de nuevo. No edites manualmente un resumen para ocultar pedidos ausentes, reemplaces el exportador o lector ni cambies los registros fuente. Tu entrega consta del query.sql reutilizable y ambos archivos CSV reales. Los archivos deben contener una fila por cada pedido fuente, las nueve columnas exactas de pedido y las ocho columnas de resumen mostradas arriba. El orden de las filas no es el resultado de negocio; lo son las identidades, los valores, las etiquetas y sus relaciones.

Resumen

Creaste una consulta CTE reutilizable, clasificaste cada pedido a partir de los cobros netos menos el valor de sus artículos y entregaste dos archivos CSV reales. Leíste etiquetas con el uso correcto de comillas y distinguiste los totales equilibrados de los puestos de los pedidos individuales saldados.