Entregar uma reconciliação de vendas

Introdução

A PAPER & PIER precisa de uma entrega de fechamento: uma linha CSV por pedido e um resumo para cada banca. As duas bancas podem ter diferença geral zero enquanto alguns pedidos individuais continuam com pagamento insuficiente ou em excesso. A entrega deve manter essas exceções, em vez de anunciar sucesso a partir de um único total geral.

Você salvará uma consulta SQL reutilizável e usará o exportador fornecido para criar arquivos CSV reais a partir dos resultados efetivos dela. Primeiro, conclua Manter o total correto de cada pedido ou conheça SUM/GROUP BY, LEFT JOIN, COALESCE e o problema de duplicação em junções. Expressões de tabela comuns e classificação de status são explicadas aqui. Use Terminal durante todo o lab; esta unidade tem seu próprio registro de vendas independente com seis pedidos.

Mantenha inalteradas as quatro tabelas de origem e as ferramentas fornecidas. Altere apenas query.sql e arquivos em reports/; não adicione objetos ao banco de dados. Todos os valores monetários são centavos inteiros. Esta é uma reconciliação simplificada de recebimentos, sem impostos nem liquidação por provedores de pagamento. O exportador trata da codificação CSV e dos cálculos do resumo; você não precisa escrever Python.

Salvar uma consulta reutilizável por pedido

Nesta etapa, você calculará os recebimentos líquidos, a diferença e o status de cada pedido usando uma única instrução SQL autossuficiente. Comece em Terminal:

cd /home/labex/project/sales-desk

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

Este registro de vendas independente tem A1 a A6, incluindo dois pedidos com o rótulo Alex Kim e rótulos contendo vírgulas ou caracteres de aspas. Os IDs distinguem os pedidos; preserve os rótulos exatamente como armazenados. Analise cada lista de fatos antes de derivar os 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, apenas pagamentos e reembolsos succeeded contam. Dois eventos bem-sucedidos com valores iguais contam ambos. Fatos elegíveis ausentes contribuem com zero. Os cálculos são:

Uma expressão de tabela comum (CTE) é um SELECT intermediário nomeado, disponível apenas dentro de uma instrução. WITH name AS (...) a introduz; vírgulas separam várias CTEs. Ela oferece o mesmo raciocínio de separar fatos das views anteriores, sem criar objetos no banco de dados nem depender de arquivos de outro lab.

Primeiro, três CTEs agregam cada fato independentemente. Depois, totals começa pelos pedidos e preenche totais ausentes com zero. balances calcula o líquido e a diferença a partir desses totais nomeados. O último SELECT usa CASE WHEN ... THEN ... ELSE ... END para escolher o status pelo sinal da diferença. Essas camadas tornam os aliases disponíveis para o SELECT seguinte, em vez de depender do alias de outra expressão na mesma lista de SELECT. SELECT * na camada intermediária de saldos carrega os totais adiante; o SELECT final lista explicitamente exatamente nove colunas na ordem exigida.

Salve a instrução completa. cat > substitui o arquivo; o here-document entre aspas preserva o SQL literalmente. Inclua a linha 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

Não inclua comandos do SQLite iniciados por ponto nem comandos do shell em query.sql: o exportador executa uma instrução SELECT/WITH comum. Ela deve funcionar a partir das quatro tabelas de origem em outro registro de vendas com o mesmo esquema, incluindo IDs de pedidos e valores diferentes. Linhas de resultado fixas no código ou views do banco de dados não forneceriam essa consulta reutilizável.

< envia este arquivo à CLI do SQLite para uma primeira execução legível:

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

Interprete as linhas reais, não apenas um total. Os saldos esperados são:

A2 permanece visível sem um pagamento. A3 mantém os dois reembolsos bem-sucedidos de 50 centavos. O reembolso com falha de A6 não reduz seus recebimentos líquidos. A1 e A2 permanecem separados, apesar de compartilharem um rótulo de cliente. As cinco colunas de centavos contêm inteiros reais, incluindo diferenças negativas; não as transforme em strings de moeda formatadas.

Para um erro de sintaxe, leia o local informado e substitua query.sql pelo bloco completo corrigido antes de executar novamente. Para um caminho incorreto, retorne usando o cd absoluto acima e use ls para encontrar query.sql. Não esvazie o registro de vendas, não modifique valores para forçar um equilíbrio nem adicione tabelas de resultados armazenados.

Entregar e consultar os arquivos CSV reais

Nesta etapa, você exportará a consulta salva e consultará seus arquivos reais de entrega, incluindo rótulos que exigem aspas no CSV. Comece em Terminal, em /home/labex/project/sales-desk.

./export é um executável fornecido no diretório atual. Ele lê query.sql, executa essa instrução sem alterações sobre ledger.db usando uma conexão somente leitura e grava as linhas efetivamente retornadas em reports/orders.csv. Ele deriva reports/summary.csv apenas dessas linhas retornadas. Não corrige uma consulta errada nem adiciona pedidos ausentes. O diretório preparado reports é o local dos dois arquivos:

./export

A mensagem informa seis linhas de consulta e nomeia os dois arquivos de saída. O leitor abaixo mostrará dois resumos de banca. Uma exportação bem-sucedida significa que os arquivos foram gravados, não que seus cálculos estão corretos. Se a consulta estiver ausente ou inválida, o exportador informará uma falha. Arquivos antigos de uma exportação anterior bem-sucedida ainda podem estar presentes: corrija a consulta salva e execute ./export novamente antes de considerá-los atuais.

./review é o leitor CSV fornecido. Ele lê os arquivos salvos sem consultar o banco de dados e exibe cada linha interpretada como um registro com campos identificados. Uma saída ausente ou alterada continua ausente ou alterada:

./review

Os seis registros de pedido devem corresponder à tabela da etapa anterior, com os rótulos originais exatos de cliente e banca. As colunas do resumo são:

North, Pier tem três pedidos: devido 1650, líquido 1650, diferença 0 e um de cada status. South também tem três: devido 1100, líquido 1100, diferença 0 e um de cada status. Nas duas bancas, há quatro pedidos não quitados, embora a diferença geral seja zero. Pagamentos em excesso e insuficientes se anulam em um total; eles não quitam o pedido de outra pessoa.

CSV significa valores separados por vírgulas, mas uma vírgula dentro de um rótulo faz parte de um único campo. O CSV padrão envolve esses campos com aspas duplas e representa uma aspa dentro de um campo com dois caracteres de aspas. cat exibe os bytes brutos do arquivo como texto; compare essa codificação com os rótulos corretamente interpretados por ./review:

cat reports/orders.csv

cat reports/summary.csv

No arquivo de pedidos, o rótulo de cliente Paper, "Pine" é codificado como "Paper, ""Pine"""; North, Pier é codificado como "North, Pier". Esses caracteres extras de aspas são sintaxe CSV, não alterações nos rótulos. Dividir cada linha em cada vírgula corromperia esses registros; o gravador e o leitor fornecidos tratam do formato.

Se um CSV estiver ausente, execute ./export novamente após corrigir query.sql e leia os dois arquivos de novo. Não edite manualmente um resumo para ocultar pedidos ausentes, não substitua o exportador/leitor nem altere registros de origem. Sua entrega consiste no query.sql reutilizável e nos dois arquivos CSV reais. Os arquivos devem conter uma linha para cada pedido de origem, as nove colunas exatas dos pedidos e as oito colunas de resumo mostradas acima. A ordem das linhas não é o resultado de negócio; as identidades, os valores, os rótulos e seus relacionamentos são.

Resumo

Você criou uma consulta CTE reutilizável, classificou cada pedido a partir dos recebimentos líquidos menos o valor de seus itens e entregou dois arquivos CSV reais. Interpretou rótulos corretamente delimitados por aspas e distinguiu totais equilibrados das bancas de pedidos individualmente quitados.