Подготовьте сверку продаж

Введение

PAPER & PIER нужен комплект результатов при закрытии: одна строка CSV на заказ и сводка по каждому киоску. У обоих киосков общая разница может быть нулевой, хотя отдельные заказы остаются недоплаченными или переплаченными. Результаты должны сохранять эти исключения, а не объявлять успех по одному общему итогу.

Вы сохраните SQL-запрос для повторного использования и с помощью предоставленного средства экспорта создадите настоящие CSV-файлы из его фактических результатов. Сначала завершите занятие «Получите правильный итог для каждого заказа» либо освойте SUM/GROUP BY, LEFT JOIN, COALESCE и проблему дублирования при соединении. Обобщённые табличные выражения и классификация статусов объясняются здесь. На протяжении всей работы используйте Terminal; у этого занятия собственный независимый журнал с шестью заказами.

Сохраните все четыре исходные таблицы и предоставленные инструменты без изменений. Меняйте только query.sql и файлы в reports/; не добавляйте объекты базы данных. Все денежные суммы — целое число центов. Это упрощённая сверка поступлений без налогов и расчётов с платёжным провайдером. Средство экспорта обрабатывает кодирование CSV и арифметику сводки; писать Python не требуется.

Сохраните запрос по заказам для повторного использования

На этом шаге вы рассчитаете чистые поступления, разницу и статус каждого заказа с помощью одной самодостаточной инструкции SQL. Начните во вкладке Terminal:

cd /home/labex/project/sales-desk

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

В этом независимом журнале есть заказы от A1 до A6, в том числе два с именем Alex Kim и текстовые значения с запятыми или кавычками. Заказы различаются по ID; сохраняйте текст точно так, как он записан. Прежде чем рассчитывать остатки, изучите каждый список фактов:

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

Как и раньше, учитываются только платежи и возвраты со статусом succeeded. Оба успешных события с равными суммами учитываются. Отсутствующие факты, удовлетворяющие условиям, дают ноль. Арифметика такова:

Обобщённое табличное выражение (CTE) — именованный промежуточный SELECT, доступный только внутри одной инструкции. WITH name AS (...) вводит его; несколько CTE разделяются запятыми. Это позволяет отдельно обрабатывать факты, как в предыдущих представлениях, не создавая объектов базы данных и не завися от файлов другого занятия.

Сначала три CTE независимо агрегируют каждый вид фактов. Затем totals начинает с заказов и заменяет отсутствующие итоги нулями. balances вычисляет чистые поступления и разницу из этих именованных итогов. Последний SELECT использует CASE WHEN ... THEN ... ELSE ... END, чтобы выбрать статус по знаку разницы. Эти слои делают псевдонимы доступными следующему SELECT, не полагаясь на псевдоним другого выражения в том же списке SELECT. SELECT * в промежуточном слое расчётов переносит итоги дальше; последний SELECT явно перечисляет ровно девять столбцов в требуемом порядке.

Сохраните полную инструкцию. cat > перезаписывает файл; here-document с маркером в кавычках сохраняет SQL буквально. Включите завершающую строку 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

Не включайте команды SQLite с точкой или команды оболочки в query.sql: средство экспорта выполняет одну обычную инструкцию SELECT/WITH. Она должна работать с четырьмя исходными таблицами другого журнала с такой же схемой, в том числе с другими ID заказов и суммами. Жёстко заданные строки результата или представления базы данных не обеспечили бы такого повторного использования запроса.

< передаёт этот файл в CLI SQLite для первого запуска с удобочитаемым выводом:

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

Читайте фактические строки, а не только итог. Ожидаемые значения:

A2 остаётся видимым без платежа. У A3 учтены оба успешных возврата по 50 центов. Неудачный возврат A6 не уменьшает его чистые поступления. A1 и A2 остаются отдельными заказами, несмотря на одинаковое имя покупателя. Все пять столбцов с центами имеют настоящий целочисленный тип, включая отрицательные разницы; не превращайте их в форматированные строки денежных сумм.

При синтаксической ошибке прочитайте указанное место ошибки и замените query.sql полным исправленным блоком перед повторным запуском. При неверном пути вернитесь с помощью абсолютной команды cd выше и используйте ls, чтобы найти query.sql. Не очищайте журнал, не меняйте суммы ради равенства и не добавляйте таблицы с сохранёнными результатами.

Подготовьте и прочитайте настоящие CSV-файлы

На этом шаге вы экспортируете сохранённый запрос и прочитаете настоящие файлы для передачи результата, включая текст, требующий кавычек в CSV. Начните во вкладке Terminal в /home/labex/project/sales-desk.

./export — предоставленный исполняемый файл в текущем каталоге. Он читает query.sql, выполняет эту неизменённую инструкцию над ledger.db через соединение только для чтения и записывает фактически возвращённые строки в reports/orders.csv. Он формирует reports/summary.csv только из этих возвращённых строк. Он не исправляет неправильный запрос и не добавляет отсутствующие заказы. Подготовленный каталог reports предназначен для обоих файлов:

./export

В сообщении указано, что запрос вернул шесть строк, и названы оба выходных файла. Приведённое ниже средство чтения покажет две сводки киосков. Успешный экспорт означает, что файлы записаны, а не что ваши расчёты верны. Если запрос отсутствует или недопустим, средство экспорта сообщает об ошибке. После предыдущего успешного экспорта могут остаться старые файлы: исправьте сохранённый запрос и снова запустите ./export, прежде чем считать их актуальными.

./review — предоставленное средство чтения CSV. Оно читает сохранённые файлы без обращения к базе данных и показывает каждую разобранную строку как запись с подписями полей. Отсутствующий или изменённый результат остаётся отсутствующим или изменённым:

./review

Шесть записей заказов должны совпадать с таблицей предыдущего шага, точно сохраняя исходные имена покупателей и названия киосков. Столбцы сводки:

У North, Pier три заказа: к оплате 1650, чистые поступления 1650, разница 0 и по одному заказу каждого статуса. У South также три: к оплате 1100, чистые поступления 1100, разница 0 и по одному заказу каждого статуса. В двух киосках всего четыре заказа с незакрытой оплатой, хотя общая разница равна нулю. Переплаты и недоплаты взаимно погашаются в итоге, но не закрывают чужой заказ.

CSV означает значения, разделённые запятыми, но запятая внутри текста — часть одного поля. Стандартный CSV окружает такие поля двойными кавычками и представляет кавычку внутри поля двумя символами кавычек. cat показывает исходные байты файла как текст; сравните это кодирование с правильно разобранными значениями из ./review:

cat reports/orders.csv

cat reports/summary.csv

В файле заказов имя покупателя Paper, "Pine" закодировано как "Paper, ""Pine"""; North, Pier закодировано как "North, Pier". Эти дополнительные кавычки — синтаксис CSV, а не изменения текста. Разделение каждой строки по каждой запятой повредило бы эти записи; предоставленные средства записи и чтения обрабатывают формат правильно.

Если CSV отсутствует, снова запустите ./export после исправления query.sql, а затем прочитайте оба файла ещё раз. Не редактируйте сводку вручную ради сокрытия отсутствующих заказов, не заменяйте средства экспорта и чтения и не меняйте исходные записи. Комплект для передачи состоит из повторно используемого query.sql и обоих настоящих CSV-файлов. Файлы должны содержать по одной строке для каждого исходного заказа, точные девять столбцов заказов и восемь столбцов сводки, показанных выше. Рабочий результат определяется не порядком строк, а идентификаторами, значениями, текстом и их связями.

Резюме

Вы создали запрос с CTE для повторного использования, определили статус каждого заказа по разнице между чистыми поступлениями и стоимостью товаров и подготовили два настоящих CSV-файла. Вы прочитали текстовые значения с правильным экранированием кавычками и научились отличать сбалансированные итоги киоска от отдельных полностью оплаченных заказов.