Advertisement
caps_lock

mig

Nov 29th, 2018
3,121
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
Rails 8.73 KB | None | 0 0
  1. class RevenueByProductBreakdowns < ActiveRecord::Migration
  2.   def up
  3.     execute <<-SQL
  4.       CREATE MATERIALIZED VIEW revenue_by_product_breaakdowns AS
  5.         SELECT (sum(payments_amount) - sum(refunds_amount) - sum(charges_amount)) AS revenue_amount,
  6.                sum(payments_amount) AS payments_amount,
  7.                sum(refunds_amount) AS refunds_amount,
  8.                sum(charges_amount) AS charges_amount,
  9.                sum(gateway_charges_amount) AS gateway_charges_amount,
  10.                sum(ticketinghub_charges_amount) AS ticketinghub_charges_amount,
  11.                channel_id,
  12.                channel_name,
  13.                user_id,
  14.                user_name,
  15.                location_id,
  16.                location_name,
  17.                reseller_id,
  18.                reseller_name,
  19.                supplier_id,
  20.                currency,
  21.                created_at_date,
  22.                payment_type
  23.         FROM
  24.         (
  25.           SELECT
  26.                sum(booking_payments.amount * orders.exchange_rate) as payments_amount,
  27.                0 AS refunds_amount,
  28.                0 AS charges_amount,
  29.                0 AS ticketinghub_charges_amount,
  30.                0 AS gateway_charges_amount,
  31.                channels.id AS channel_id,
  32.                channels.name AS channel_name,
  33.                users.id AS user_id,
  34.                users.full_name AS user_name,
  35.                locations.id AS location_id,
  36.                locations.name AS location_name,
  37.                resellers.id AS reseller_id,
  38.                resellers.name AS reseller_name,
  39.                orders.supplier_id as supplier_id,
  40.                orders.currency AS currency,
  41.                payments.created_at::date AS created_at_date,
  42.                payments.type AS payment_type,
  43.                products.id as product_id
  44.           FROM "booking_payments"
  45.           JOIN "payments" ON "payments"."id" = "booking_payments"."payment_id"
  46.           LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
  47.           LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_payments"."booking_id"
  48.           LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
  49.           LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
  50.           LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
  51.           LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
  52.           LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
  53.           WHERE payments.confirmed_at IS NOT NULL
  54.           GROUP BY channels.id,
  55.                    channels.name,
  56.                    users.id,
  57.                    users.full_name,
  58.                    locations.id,
  59.                    locations.name,
  60.                    resellers.id,
  61.                    resellers.name,
  62.                    payments.created_at::date,
  63.                    orders.supplier_id,
  64.                    orders.currency,
  65.                    payments.type,
  66.                    products.id
  67.  
  68.           UNION ALL
  69.  
  70.             SELECT 0 AS payments_amount,
  71.                   sum(booking_refunds.amount * orders.exchange_rate) as refunds_amount,
  72.                    0 AS charges_amount,
  73.                    0 AS ticketinghub_charges_amount,
  74.                    0 AS gateway_charges_amount,
  75.                    channels.id AS channel_id,
  76.                    channels.name AS channel_name,
  77.                    users.id AS user_id,
  78.                    users.full_name AS user_name,
  79.                    locations.id AS location_id,
  80.                    locations.name AS location_name,
  81.                    resellers.id AS reseller_id,
  82.                    resellers.name AS reseller_name,
  83.                    orders.supplier_id as supplier_id,
  84.                    orders.currency AS currency,
  85.                    refunds.created_at::date AS created_at_date,
  86.                    payments.type AS payment_type,
  87.                    products.id AS product_id
  88.             FROM "booking_refunds"
  89.             JOIN "refunds" ON "refunds"."id" = "booking_refunds"."refund_id"
  90.             LEFT OUTER JOIN "payments" ON "payments"."id" = "refunds"."payment_id"
  91.             LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
  92.             LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_refunds"."booking_id"
  93.             LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
  94.             LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
  95.             LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
  96.             LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
  97.             LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
  98.             GROUP BY channels.id,
  99.                      channels.name,
  100.                      users.id,
  101.                      users.full_name,
  102.                      locations.id,
  103.                      locations.name,
  104.                      resellers.id,
  105.                      resellers.name,
  106.                      refunds.created_at::date,
  107.                      orders.supplier_id,
  108.                      orders.currency,
  109.                      payments.type,
  110.                      products.id
  111.  
  112.           UNION ALL
  113.  
  114.             SELECT 0 AS payments_amount,
  115.                    0 AS refunds_amount,
  116.                    sum(charges.amount * charges.exchange_rate) as charges_amount,
  117.                    sum(CASE when (payee = 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as ticketinghub_charges_amount,
  118.                    sum(CASE when (payee != 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as gateway_charges_amount,
  119.                    channels.id AS channel_id,
  120.                    channels.name AS channel_name,
  121.                    users.id AS user_id,
  122.                    users.full_name AS user_name,
  123.                    locations.id AS location_id,
  124.                    locations.name AS location_name,
  125.                    resellers.id AS reseller_id,
  126.                    resellers.name AS reseller_name,
  127.                    orders.supplier_id as supplier_id,
  128.                    orders.currency AS currency,
  129.                    charges.created_at::date AS created_at_date,
  130.                    payments.type AS payment_type
  131.             FROM "charges"
  132.             LEFT OUTER JOIN "adjustments" ON "adjustments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Adjustment'
  133.             LEFT OUTER JOIN "payments" ON "payments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Payment'
  134.             LEFT OUTER JOIN "refunds" ON "refunds"."id" = "charges"."context_id" AND "charges"."context_type" = 'Refund'
  135.             LEFT OUTER JOIN "payments" AS "refunds_payments" ON "refunds"."payment_id" = "refunds_payments"."id"
  136.             LEFT OUTER JOIN "disputes" ON "disputes"."id" = "adjustments"."dispute_id"
  137.             LEFT OUTER JOIN "payments" AS "disputes_payments" ON "disputes"."id" = "disputes_payments"."dispute_id"
  138.             LEFT OUTER JOIN "orders" ON ("orders"."id" = "payments"."order_id" OR "orders"."id" = "disputes_payments"."order_id" OR "orders"."id" = "refunds_payments"."order_id")
  139.             LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
  140.             LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
  141.             LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
  142.             LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
  143.             GROUP BY channels.id,
  144.                      channels.name,
  145.                      users.id,
  146.                      users.full_name,
  147.                      locations.id,
  148.                      locations.name,
  149.                      resellers.id,
  150.                      resellers.name,
  151.                      charges.created_at::date,
  152.                      orders.supplier_id,
  153.                      orders.currency,
  154.                      payments.type
  155.           ) x
  156.           GROUP BY channel_id,
  157.                    channel_name,
  158.                    user_id,
  159.                    user_name,
  160.                    location_id,
  161.                    location_name,
  162.                    reseller_id,
  163.                    reseller_name,
  164.                    supplier_id,
  165.                    currency,
  166.                    created_at_date,
  167.                    payment_type
  168.     ;
  169.     SQL
  170.  
  171.     add_index :revenue_breakdowns, :supplier_id
  172.     add_index :revenue_breakdowns, [:supplier_id, :created_at_date]
  173.     add_index :revenue_breakdowns, :reseller_id
  174.     add_index :revenue_breakdowns, :location_id
  175.     add_index :revenue_breakdowns, :user_id
  176.     add_index :revenue_breakdowns, :channel_id
  177.     add_index :revenue_breakdowns, :payment_type
  178.   end
  179.  
  180.   def down
  181.     execute "DROP MATERIALIZED VIEW revenue_breakdowns;"
  182.   end
  183.  
  184. end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement