Snippets

Thiago Paim SQL de pedidos

Created by Thiago Paim
snippet.sql
Raw 
 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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
# Versão resumida do relatório de marketing

SELECT o.id,
    o.order_id AS codigo_pedido,
    o.status AS status,
    o.freight_payment_type AS tipo_pagamento,
    o.is_first AS primeiro_frete_fin,

    CONVERT_TZ(o.date_paid_cargobr, 'GMT', 'America/Sao_Paulo') AS pago_cargobr,
    CONVERT_TZ(o.date_added, 'GMT', 'America/Sao_Paulo') AS data_criacao,
    CONVERT_TZ(o.ship_date, 'GMT', 'America/Sao_Paulo') AS coleta_prevista,
    CONVERT_TZ(o.real_date_collected, 'GMT', 'America/Sao_Paulo') AS coleta_executada,
    CONVERT_TZ(o.date_delivered_marked, 'GMT', 'America/Sao_Paulo') AS entrega_prevista,
    o.real_date_delivered AS entrega_executada,

    CONVERT_TZ(b.date_confirmed , 'GMT', 'America/Sao_Paulo') AS data_fechamento,
    b.value AS valor_frete,
    b.raw_value_icms AS valor_transportadora,
    b.cte_value AS valor_cte,
    b.real_value AS valor_real,
    bids.contagem AS qnt_bids,

    c.transport_invoice AS numero_cte,
    c.invoice_value AS valor_nf,
    c.invoice_number AS numero_nf,
    c.description AS perfil_de_carga,
    c.cargo_type AS slug_perfil_de_carga,

    s.exhibition_name AS nome_fantasia,
    s.company_name AS razao_social,
    CONVERT_TZ(s.date_joined, 'GMT', 'America/Sao_Paulo') AS data_cadastro_embarcador,

    dispatcher.zipcode AS origem_cep,
    origin_city.nome AS origem,
    origin_state.sigla AS origem_estado,
    receiver.zipcode AS destino_cep,
    destination_city.nome AS destino,
    destination_state.sigla AS destino_estado,
    carrier.exhibition_name AS nome_transportadora,

    item.total_weight,
    item.total_cubage

FROM orders_order AS o
LEFT JOIN accounts_company AS s ON o.shipper_id = s.id
LEFT JOIN orders_cargo AS c ON c.order_id = o.id
LEFT JOIN orders_bid AS b ON b.order_id = o.id AND b.confirmed = 1
LEFT JOIN accounts_company AS carrier ON b.company_id = carrier.id

LEFT JOIN (  -- Counting bids
    SELECT
       order_id,
       COUNT(*) as contagem

    FROM orders_bid AS bid
    GROUP BY order_id
) AS bids ON bids.order_id = o.id

LEFT JOIN accounts_company AS dispatcher ON o.dispatcher_id = dispatcher.id
LEFT JOIN ibge_municipio AS origin_city ON dispatcher.city_id = origin_city.codigo_ibge
LEFT JOIN ibge_unidadefederativa AS origin_state ON origin_city.uf_id = origin_state.codigo_ibge

LEFT JOIN accounts_company AS receiver ON o.receiver_id = receiver.id
LEFT JOIN ibge_municipio AS destination_city ON receiver.city_id = destination_city.codigo_ibge
LEFT JOIN ibge_unidadefederativa AS destination_state ON destination_city.uf_id = destination_state.codigo_ibge

INNER JOIN (  -- Obtendo as medidas dos volumes
    SELECT
    item.id,
    item.cargo_id,
    item.items_amount,
    item.weight,
    item.height,
    item.width,
    item.length,
    SUM(item.weight * item.items_amount) AS total_weight,
    SUM(item.height * item.width * item.length * 0.0003 * item.items_amount) AS total_cubage

    FROM orders_cargoitem AS item
    GROUP BY cargo_id
) AS item ON item.cargo_id = c.id

WHERE o.date_added > CONVERT_TZ('2019-01-01', 'GMT', 'America/Sao_Paulo')

Comments (0)

HTTPS SSH

You can clone a snippet to your computer for local editing. Learn more.