50 lines
2.8 KiB
SQL
50 lines
2.8 KiB
SQL
-- P5-4018: only include orders that are ready for processing and paid in the order_not_delivered view
|
|
|
|
DROP VIEW IF EXISTS v_analytics_orders_not_delivered;
|
|
|
|
CREATE VIEW v_analytics_orders_not_delivered AS
|
|
SELECT
|
|
CASE
|
|
WHEN country_code.value IS NULL THEN 'Unknown'
|
|
WHEN country_code.value = 'GB' THEN 'Photowall UK'
|
|
WHEN country_code.value in ('SE','NO','DE','AT','FR','ES','FI','DK','PL','US','NL') THEN 'Photowall '||country_code.value
|
|
ELSE 'Photowall OTHER'
|
|
END AS account,
|
|
orders.inserted::DATE AS orderdate,
|
|
orders.orderid,
|
|
md5(firstname.value||'secret') AS first_name,
|
|
md5(lastname.value||'secret') AS last_name,
|
|
md5(company.value||'secret') AS company,
|
|
md5(address.value||'secret') AS address,
|
|
zipcode.value AS postal_code,
|
|
city.value AS city,
|
|
country_code.value AS country,
|
|
md5(email.value||'secret') AS email,
|
|
order_sum.value::float / exchange_rate.value::float as order_value_sek
|
|
from
|
|
"order-orders" orders
|
|
left join "order-orders_fields" confirmed on orders.orderid = confirmed.orderid AND confirmed.fieldid = 175
|
|
left join "order-orders_fields" delivered on orders.orderid = delivered.orderid AND delivered.fieldid = 176
|
|
left join "order-orders_fields" paid on orders.orderid = paid.orderid AND paid.fieldid = 168
|
|
left join "order-orders_fields" firstname on orders.orderid = firstname.orderid AND firstname.fieldid = 156
|
|
left join "order-orders_fields" lastname on orders.orderid = lastname.orderid AND lastname.fieldid = 157
|
|
left join "order-orders_fields" company on orders.orderid = company.orderid AND company.fieldid = 177
|
|
left join "order-orders_fields" address on orders.orderid = address.orderid AND address.fieldid = 158
|
|
left join "order-orders_fields" zipcode on orders.orderid = zipcode.orderid AND zipcode.fieldid = 159
|
|
left join "order-orders_fields" city on orders.orderid = city.orderid AND city.fieldid = 160
|
|
left join "order-orders_fields" country_code on orders.orderid = country_code.orderid AND country_code.fieldid = 151
|
|
left join "order-orders_fields" email on orders.orderid = email.orderid AND email.fieldid = 184
|
|
left join "order-orders_fields" order_sum on orders.orderid = order_sum.orderid AND order_sum.fieldid = 246
|
|
left join "order-orders_fields" exchange_rate on orders.orderid = exchange_rate.orderid AND exchange_rate.fieldid = 171
|
|
left join "order-orders_fields" canceled on orders.orderid = canceled.orderid AND canceled.fieldid = 200
|
|
left join "order-orders_fields" ready_for_handling on orders.orderid = ready_for_handling.orderid AND ready_for_handling.fieldid = 195
|
|
where
|
|
confirmed.value = 'true'
|
|
and delivered.value = 'false'
|
|
and canceled.value is null
|
|
and paid.value = 'true'
|
|
and ready_for_handling.value = 'true'
|
|
;
|
|
|
|
GRANT SELECT ON v_analytics_orders_not_delivered TO datastudio;
|