Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- class RevenueByProductBreakdowns < ActiveRecord::Migration
- def up
- execute <<-SQL
- CREATE MATERIALIZED VIEW revenue_by_product_breaakdowns AS
- SELECT (sum(payments_amount) - sum(refunds_amount) - sum(charges_amount)) AS revenue_amount,
- sum(payments_amount) AS payments_amount,
- sum(refunds_amount) AS refunds_amount,
- sum(charges_amount) AS charges_amount,
- sum(gateway_charges_amount) AS gateway_charges_amount,
- sum(ticketinghub_charges_amount) AS ticketinghub_charges_amount,
- channel_id,
- channel_name,
- user_id,
- user_name,
- location_id,
- location_name,
- reseller_id,
- reseller_name,
- supplier_id,
- currency,
- created_at_date,
- payment_type
- FROM
- (
- SELECT
- sum(booking_payments.amount * orders.exchange_rate) as payments_amount,
- 0 AS refunds_amount,
- 0 AS charges_amount,
- 0 AS ticketinghub_charges_amount,
- 0 AS gateway_charges_amount,
- channels.id AS channel_id,
- channels.name AS channel_name,
- users.id AS user_id,
- users.full_name AS user_name,
- locations.id AS location_id,
- locations.name AS location_name,
- resellers.id AS reseller_id,
- resellers.name AS reseller_name,
- orders.supplier_id as supplier_id,
- orders.currency AS currency,
- payments.created_at::date AS created_at_date,
- payments.type AS payment_type,
- products.id as product_id
- FROM "booking_payments"
- JOIN "payments" ON "payments"."id" = "booking_payments"."payment_id"
- LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
- LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_payments"."booking_id"
- LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
- LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
- LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
- LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
- LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
- WHERE payments.confirmed_at IS NOT NULL
- GROUP BY channels.id,
- channels.name,
- users.id,
- users.full_name,
- locations.id,
- locations.name,
- resellers.id,
- resellers.name,
- payments.created_at::date,
- orders.supplier_id,
- orders.currency,
- payments.type,
- products.id
- UNION ALL
- SELECT 0 AS payments_amount,
- sum(booking_refunds.amount * orders.exchange_rate) as refunds_amount,
- 0 AS charges_amount,
- 0 AS ticketinghub_charges_amount,
- 0 AS gateway_charges_amount,
- channels.id AS channel_id,
- channels.name AS channel_name,
- users.id AS user_id,
- users.full_name AS user_name,
- locations.id AS location_id,
- locations.name AS location_name,
- resellers.id AS reseller_id,
- resellers.name AS reseller_name,
- orders.supplier_id as supplier_id,
- orders.currency AS currency,
- refunds.created_at::date AS created_at_date,
- payments.type AS payment_type,
- products.id AS product_id
- FROM "booking_refunds"
- JOIN "refunds" ON "refunds"."id" = "booking_refunds"."refund_id"
- LEFT OUTER JOIN "payments" ON "payments"."id" = "refunds"."payment_id"
- LEFT OUTER JOIN "orders" ON "orders"."id" = "payments"."order_id"
- LEFT OUTER JOIN "bookings" ON "bookings"."id" = "booking_refunds"."booking_id"
- LEFT OUTER JOIN "products" ON "products"."id" = "bookings"."product_id"
- LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
- LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
- LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
- LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
- GROUP BY channels.id,
- channels.name,
- users.id,
- users.full_name,
- locations.id,
- locations.name,
- resellers.id,
- resellers.name,
- refunds.created_at::date,
- orders.supplier_id,
- orders.currency,
- payments.type,
- products.id
- UNION ALL
- SELECT 0 AS payments_amount,
- 0 AS refunds_amount,
- sum(charges.amount * charges.exchange_rate) as charges_amount,
- sum(CASE when (payee = 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as ticketinghub_charges_amount,
- sum(CASE when (payee != 'ticketinghub') THEN charges.amount * charges.exchange_rate ELSE 0 END) as gateway_charges_amount,
- channels.id AS channel_id,
- channels.name AS channel_name,
- users.id AS user_id,
- users.full_name AS user_name,
- locations.id AS location_id,
- locations.name AS location_name,
- resellers.id AS reseller_id,
- resellers.name AS reseller_name,
- orders.supplier_id as supplier_id,
- orders.currency AS currency,
- charges.created_at::date AS created_at_date,
- payments.type AS payment_type
- FROM "charges"
- LEFT OUTER JOIN "adjustments" ON "adjustments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Adjustment'
- LEFT OUTER JOIN "payments" ON "payments"."id" = "charges"."context_id" AND "charges"."context_type" = 'Payment'
- LEFT OUTER JOIN "refunds" ON "refunds"."id" = "charges"."context_id" AND "charges"."context_type" = 'Refund'
- LEFT OUTER JOIN "payments" AS "refunds_payments" ON "refunds"."payment_id" = "refunds_payments"."id"
- LEFT OUTER JOIN "disputes" ON "disputes"."id" = "adjustments"."dispute_id"
- LEFT OUTER JOIN "payments" AS "disputes_payments" ON "disputes"."id" = "disputes_payments"."dispute_id"
- LEFT OUTER JOIN "orders" ON ("orders"."id" = "payments"."order_id" OR "orders"."id" = "disputes_payments"."order_id" OR "orders"."id" = "refunds_payments"."order_id")
- LEFT OUTER JOIN "channels" ON "channels"."id" = "orders"."channel_id"
- LEFT OUTER JOIN "users" ON "users"."id" = "orders"."user_id"
- LEFT OUTER JOIN "locations" ON "locations"."id" = "orders"."location_id"
- LEFT OUTER JOIN "resellers" ON "resellers"."id" = "orders"."reseller_id"
- GROUP BY channels.id,
- channels.name,
- users.id,
- users.full_name,
- locations.id,
- locations.name,
- resellers.id,
- resellers.name,
- charges.created_at::date,
- orders.supplier_id,
- orders.currency,
- payments.type
- ) x
- GROUP BY channel_id,
- channel_name,
- user_id,
- user_name,
- location_id,
- location_name,
- reseller_id,
- reseller_name,
- supplier_id,
- currency,
- created_at_date,
- payment_type
- ;
- SQL
- add_index :revenue_breakdowns, :supplier_id
- add_index :revenue_breakdowns, [:supplier_id, :created_at_date]
- add_index :revenue_breakdowns, :reseller_id
- add_index :revenue_breakdowns, :location_id
- add_index :revenue_breakdowns, :user_id
- add_index :revenue_breakdowns, :channel_id
- add_index :revenue_breakdowns, :payment_type
- end
- def down
- execute "DROP MATERIALIZED VIEW revenue_breakdowns;"
- end
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement