Location Transactions

view: location_transactions {
  sql_table_name: ${TABLE}.location_transactions ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: monthly_booking_id_old {
    type: int
    hidden: yes
    sql: ${TABLE}.monthly_booking_id ;;
  }

  dimension: booking_id {
    type: int
    sql: CASE WHEN ${TABLE}.booking_id is not null THEN ${TABLE}.booking_id
WHEN ${TABLE}.monthly_booking_id is not null THEN ${TABLE}.monthly_booking_id
ELSE NULL END ;;
  }

  dimension: amount {
    type: number
    sql: ${TABLE}.amount ;;
  }

  dimension: booking_id_old {
    type: int
    hidden: yes
    sql: ${TABLE}.booking_id ;;
  }

  dimension: location_id {
    type: int
    sql: ${TABLE}.location_id ;;
  }

  dimension: memo {
    sql: ${TABLE}.memo ;;
  }

  dimension: reversal_of_id {
    type: int
    sql: ${TABLE}.reversal_of_id ;;
  }

  dimension: type {
    sql: ${TABLE}.type ;;
  }

  dimension: coupon_code {
    sql: ${TABLE}.coupon_code ;;
  }

  dimension: withdrawal_id {
    type: int
    sql: ${TABLE}.withdrawal_id ;;
  }

  dimension: gross_revenue_filter {
    type: yesno
    hidden: yes
    sql: ${TABLE}.type = 'sale' or (${TABLE}.type = 'coupon' and ${TABLE}.coupon_code in ('<% coupon_code_placeholder1 %>','<% coupon_code_placeholder2 %>','<% coupon_code_placeholder3 %>','<% coupon_code_placeholder4 %>','<% coupon_code_placeholder5 %>')) ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, date, week, month, day_of_month]
    sql: ${TABLE}.created_at ;;
  }

  dimension_group: post {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.post_time ;;
  }

  measure: count {
    type: count
    drill_fields: [detail*]
  }

  measure: gross_revenue_ex_coupon {
    label: "Gross Revenue (excl. coupon)"
    type: sum
    filters: [type: "sale, coupon"]
    sql: CASE WHEN ${bookings.parking_type} IN ('transient','event') THEN COALESCE(${partner_passes.payout},${amount},0) ELSE COALESCE(${partner_passes.payout},${amount},${bookings.price}) END ;;
  }

  measure: gross_revenue {
    type: sum
    filters: [gross_revenue_filter: "True"]
    sql: CASE WHEN ${bookings.parking_type} IN ('transient','event') THEN COALESCE(${partner_passes.payout},${amount},0) ELSE COALESCE(${partner_passes.payout}, ${amount},${bookings.amount_paid},0) END ;;
  }

  measure: net_revenue_simple {
    type: sum
    filters: [type: "seller_fee,buyer_fee,seller_coupon"]
    drill_fields: [locations.city, net_revenue_simple]
    sql: -1.0*${amount} ;;
  }

  measure: net_revenue_pt1 {
    type: sum
    hidden: yes
    filters: [type: "seller_fee,buyer_fee,seller_coupon"]
    sql: COALESCE(-1.0*${amount},0) ;;
  }

  measure: net_revenue_pt2 {
    type: sum
    hidden: yes
    filters: [type: "coupon"]
    filters: [coupon_code: "<% coupon_code_placeholder1 %>,<% coupon_code_placeholder2 %>,<% coupon_code_placeholder3 %>,<% coupon_code_placeholder4 %>,<% coupon_code_placeholder5 %>"]
    sql: COALESCE(${amount},0) ;;
  }

  measure: net_revenue {
    type: sum
    sql: ${net_revenue_pt1} + ${net_revenue_pt2} ;;
  }

  measure: coupon_amount {
    type: sum
    filters: [type: "coupon, seller_coupon"]
    filters: [coupon_code: "-<% coupon_code_placeholder1 %>,-<% coupon_code_placeholder2 %>,-<% coupon_code_placeholder3 %>,-<% coupon_code_placeholder4 %>,-<% coupon_code_placeholder5 %>"]
    sql: ${amount} ;;
  }
}

- view: location_transactions
  sql_table_name: ${TABLE}.location_transactions
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: monthly_booking_id_old
    type: int
    hidden: true
    sql: ${TABLE}.monthly_booking_id
  - dimension: booking_id
    type: int
    sql: CASE WHEN ${TABLE}.booking_id is not null THEN ${TABLE}.booking_id
WHEN ${TABLE}.monthly_booking_id is not null THEN ${TABLE}.monthly_booking_id
ELSE NULL END
  - dimension: amount
    type: number
    sql: ${TABLE}.amount
  - dimension: booking_id_old
    type: int
    hidden: true
    sql: ${TABLE}.booking_id
  - dimension: location_id
    type: int
    sql: ${TABLE}.location_id
  - dimension: memo
    sql: ${TABLE}.memo
  - dimension: reversal_of_id
    type: int
    sql: ${TABLE}.reversal_of_id
  - dimension: type
    sql: ${TABLE}.type
  - dimension: coupon_code
    sql: ${TABLE}.coupon_code
  - dimension: withdrawal_id
    type: int
    sql: ${TABLE}.withdrawal_id
  - dimension: gross_revenue_filter
    type: yesno
    hidden: true
    sql: ${TABLE}.type = 'sale' or (${TABLE}.type = 'coupon' and ${TABLE}.coupon_code in ('<% coupon_code_placeholder1 %>','<% coupon_code_placeholder2 %>','<% coupon_code_placeholder3 %>','<% coupon_code_placeholder4 %>','<% coupon_code_placeholder5 %>'))
  - dimension_group: created
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    - day_of_month
    sql: ${TABLE}.created_at
  - dimension_group: post
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}.post_time
  - measure: count
    type: count
    drill_fields:
    - detail*
  - measure: gross_revenue_ex_coupon
    type: sum
    label: Gross Revenue (excl. coupon)
    filters:
    - type: sale, coupon
    sql: CASE WHEN ${bookings.parking_type} IN ('transient','event') THEN COALESCE(${partner_passes.payout},${amount},0) ELSE COALESCE(${partner_passes.payout},${amount},${bookings.price}) END
  - measure: gross_revenue
    type: sum
    filters:
    - gross_revenue_filter: True
    sql: CASE WHEN ${bookings.parking_type} IN ('transient','event') THEN COALESCE(${partner_passes.payout},${amount},0) ELSE COALESCE(${partner_passes.payout}, ${amount},${bookings.amount_paid},0) END
  - measure: net_revenue_simple
    type: sum
    drill_fields:
    - locations.city
    - net_revenue_simple
    filters:
    - type: seller_fee,buyer_fee,seller_coupon
    sql: -1.0*${amount}
  - measure: net_revenue_pt1
    type: sum
    filters:
    - type: seller_fee,buyer_fee,seller_coupon
    sql: COALESCE(-1.0*${amount},0)
  - measure: net_revenue_pt2
    type: sum
    filters:
    - type: coupon
    - coupon_code: '<% coupon_code_placeholder1 %>,<% coupon_code_placeholder2 %>,<% coupon_code_placeholder3 %>,<% coupon_code_placeholder4 %>,<% coupon_code_placeholder5 %>'
    sql: COALESCE(${amount},0)
  - measure: net_revenue
    type: sum
    sql: ${net_revenue_pt1} + ${net_revenue_pt2}
  - measure: coupon_amount
    type: sum
    filters:
    - type: coupon, seller_coupon
    - coupon_code: '-<% coupon_code_placeholder1 %>,-<% coupon_code_placeholder2 %>,-<% coupon_code_placeholder3 %>,-<% coupon_code_placeholder4 %>,-<% coupon_code_placeholder5 %>'
    sql: ${amount}