Transaction Logs

view: transaction_logs {
  sql_table_name: "TransactionLogs" ;;

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

  dimension: activity_log_id {
    type: int
    sql: ${TABLE}."ActivityLogId" ;;
  }

  dimension: applied_fixed_fee {
    type: number
    value_format: "0.00"
    sql: ${TABLE}."appliedFixedFee" ;;
  }

  dimension: applied_min_payout {
    type: number
    value_format: "0.00"
    sql: ${TABLE}."appliedMinPayout" ;;
  }

  dimension: applied_percentage_fee {
    type: number
    value_format: "0.00%"
    sql: ${TABLE}."appliedPercentageFee" ;;
  }

  dimension: authorization_code {
    sql: ${TABLE}."authorizationCode" ;;
  }

  dimension: authorized_amount {
    type: number
    sql: CAST(${TABLE}."authorizedAmount" as NUMERIC) ;;
  }

  dimension: auth_amt_tier {
    type: tier
    sql: ${authorized_amount} ;;
  }

  dimension: authorized_currency {
    sql: ${TABLE}."authorizedCurrency" ;;
  }

  dimension: customer_payout {
    type: number
    value_format: "$0.00"
    sql: ${TABLE}."customerPayout" ;;
  }

  dimension: decline_code {
    sql: ${TABLE}."declineCode" ;;
  }

  dimension: decline_message {
    sql: ${TABLE}."declineMessage" ;;
  }

  dimension: unit_position_estimate {
    type: number
    sql: ${TABLE}."unitPositionEstimate" ;;
  }

  dimension: error {
    sql: ${TABLE}."error" ;;
  }

  dimension: processing_fault_error {
    type: yesno
    sql: ${error} LIKE 'processingFaultError%' ;;
  }

  dimension: exception {
    type: yesno
    sql: ${TABLE}."exception" ;;
  }

  dimension: exception_reason {
    sql: ${TABLE}."exceptionReason" ;;
  }

  dimension: expiration {
    sql: ${TABLE}."expiration" ;;
  }

  dimension: first_name {
    sql: ${TABLE}."firstName" ;;
  }

  dimension: interchange_code {
    sql: ${TABLE}."interchangeCode" ;;
  }

  dimension: interchange_fee {
    type: number
    value_format: "0.00"
    sql: ${TABLE}."interchangeFee" ;;
  }

  dimension: unit_id {
    type: int
    sql: ${TABLE}."UnitId" ;;
  }

  dimension: job_id {
    sql: ${TABLE}."jobId" ;;
  }

  dimension: last_name {
    sql: ${TABLE}."lastName" ;;
  }

  dimension: full_name {
    sql: ${first_name} ||' ' || ${last_name} ;;
  }

  dimension: log {
    sql: ${TABLE}."log" ;;
  }

  dimension: org_unit_id {
    type: int
    sql: ${TABLE}."OrgUnitId" ;;
  }

  dimension: refrence_number {
    sql: ${TABLE}."refrenceNumber" ;;
  }

  dimension: reversed_by_job_id {
    sql: ${TABLE}."reversedByJobId" ;;
  }

  dimension: settlement_log {
    sql: ${TABLE}."settlementLog" ;;
  }

  dimension: statement_id {
    type: int
    sql: ${TABLE}."StatementId" ;;
  }

  dimension: surcharge_adjustment {
    type: number
    value_format: "0.00"
    sql: ${TABLE}."surchargeAdjustment" ;;
  }

  dimension: transaction_cost {
    type: number
    value_format: "0.00"
    sql: ${TABLE}."transactionCost" ;;
  }

  dimension: transaction_id {
    sql: ${TABLE}."transactionId" ;;
  }

  dimension: transaction_status {
    sql: ${TABLE}."transactionStatus" ;;
  }

  dimension: is_settled {
    type: yesno
    sql: ${transaction_status} = 'settled' and ${reversed_by_job_id} is null ;;
  }

  dimension: transaction_timestamp {
    sql: ${TABLE}."transactionTimestamp" ;;
  }

  dimension: truncated_pan {
    sql: ${TABLE}."truncatedPan" ;;
  }

  dimension: processing_request_id {
    sql: ${TABLE}."processingRequestId" ;;
  }

  dimension: org_name {
    sql: ${TABLE}."log" -> 'unit' -> 'OrgUnit' -> 'Organization' ->> 'name' ;;
  }

  dimension: unit_name {
    sql: ${TABLE}.log->'unit'->'OrgUnit'->>'publicName' ;;
  }

  dimension: org_loc {
    sql: ${TABLE}.log->'unit'->'OrgUnit'->'OrgLocation'->>'name' ;;
  }

  dimension: org_id {
    sql: (${TABLE}.log->'unit'->'OrgUnit'->>'OrganizationId')::numeric ;;
  }

  dimension_group: created {
    type: time
    timeframes: [time, hour, date, week, month]
    sql: ${TABLE}."createdAt" ;;
  }

  dimension_group: updated {
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}."updatedAt" ;;
  }

  measure: count {
    type: count
    value_format: "#,##0"
    drill_fields: [detail*]
  }

  measure: settled_count {
    type: count
    value_format: "#,##0"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
  }

  measure: total_net_payout {
    type: sum
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${customer_payout} ;;
  }

  measure: total_authorized_amount {
    type: sum
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${authorized_amount} ;;
  }

  measure: total_settled_amount {
    type: sum
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${authorized_amount} ;;
  }

  measure: avg_settled_amount {
    type: avg
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${authorized_amount} ;;
  }

  measure: max_settled_amount {
    type: max
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${authorized_amount} ;;
  }

  measure: min_settled_amount {
    type: min
    value_format: "$#,##0.00"
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${authorized_amount} ;;
  }

  measure: unique_payers {
    type: count_distinct
    filters: [is_settled: "True"]
    drill_fields: [detail*]
    sql: ${full_name} ;;
  }
}

- view: transaction_logs
  sql_table_name: '"TransactionLogs"'
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}."id"
  - dimension: activity_log_id
    type: int
    sql: ${TABLE}."ActivityLogId"
  - dimension: applied_fixed_fee
    type: number
    value_format: 0.00
    sql: ${TABLE}."appliedFixedFee"
  - dimension: applied_min_payout
    type: number
    value_format: 0.00
    sql: ${TABLE}."appliedMinPayout"
  - dimension: applied_percentage_fee
    type: number
    value_format: '0.00%'
    sql: ${TABLE}."appliedPercentageFee"
  - dimension: authorization_code
    sql: ${TABLE}."authorizationCode"
  - dimension: authorized_amount
    type: number
    sql: CAST(${TABLE}."authorizedAmount" as NUMERIC)
  - dimension: auth_amt_tier
    type: tier
    sql: ${authorized_amount}
  - dimension: authorized_currency
    sql: ${TABLE}."authorizedCurrency"
  - dimension: customer_payout
    type: number
    value_format: $0.00
    sql: ${TABLE}."customerPayout"
  - dimension: decline_code
    sql: ${TABLE}."declineCode"
  - dimension: decline_message
    sql: ${TABLE}."declineMessage"
  - dimension: unit_position_estimate
    type: number
    sql: ${TABLE}."unitPositionEstimate"
  - dimension: error
    sql: ${TABLE}."error"
  - dimension: processing_fault_error
    type: yesno
    sql: ${error} LIKE 'processingFaultError%'
  - dimension: exception
    type: yesno
    sql: ${TABLE}."exception"
  - dimension: exception_reason
    sql: ${TABLE}."exceptionReason"
  - dimension: expiration
    sql: ${TABLE}."expiration"
  - dimension: first_name
    sql: ${TABLE}."firstName"
  - dimension: interchange_code
    sql: ${TABLE}."interchangeCode"
  - dimension: interchange_fee
    type: number
    value_format: 0.00
    sql: ${TABLE}."interchangeFee"
  - dimension: unit_id
    type: int
    sql: ${TABLE}."UnitId"
  - dimension: job_id
    sql: ${TABLE}."jobId"
  - dimension: last_name
    sql: ${TABLE}."lastName"
  - dimension: full_name
    sql: ${first_name} ||' ' || ${last_name}
  - dimension: log
    sql: ${TABLE}."log"
  - dimension: org_unit_id
    type: int
    sql: ${TABLE}."OrgUnitId"
  - dimension: refrence_number
    sql: ${TABLE}."refrenceNumber"
  - dimension: reversed_by_job_id
    sql: ${TABLE}."reversedByJobId"
  - dimension: settlement_log
    sql: ${TABLE}."settlementLog"
  - dimension: statement_id
    type: int
    sql: ${TABLE}."StatementId"
  - dimension: surcharge_adjustment
    type: number
    value_format: 0.00
    sql: ${TABLE}."surchargeAdjustment"
  - dimension: transaction_cost
    type: number
    value_format: 0.00
    sql: ${TABLE}."transactionCost"
  - dimension: transaction_id
    sql: ${TABLE}."transactionId"
  - dimension: transaction_status
    sql: ${TABLE}."transactionStatus"
  - dimension: is_settled
    type: yesno
    sql: ${transaction_status} = 'settled' and ${reversed_by_job_id} is null
  - dimension: transaction_timestamp
    sql: ${TABLE}."transactionTimestamp"
  - dimension: truncated_pan
    sql: ${TABLE}."truncatedPan"
  - dimension: processing_request_id
    sql: ${TABLE}."processingRequestId"
  - dimension: org_name
    sql: ${TABLE}."log" -> 'unit' -> 'OrgUnit' -> 'Organization' ->> 'name'
  - dimension: unit_name
    sql: ${TABLE}.log->'unit'->'OrgUnit'->>'publicName'
  - dimension: org_loc
    sql: ${TABLE}.log->'unit'->'OrgUnit'->'OrgLocation'->>'name'
  - dimension: org_id
    sql: (${TABLE}.log->'unit'->'OrgUnit'->>'OrganizationId')::numeric
  - dimension_group: created
    type: time
    timeframes:
    - time
    - hour
    - date
    - week
    - month
    sql: ${TABLE}."createdAt"
  - dimension_group: updated
    type: time
    timeframes:
    - time
    - date
    - week
    - month
    sql: ${TABLE}."updatedAt"
  - measure: count
    type: count
    value_format: '#,##0'
    drill_fields:
    - detail*
  - measure: settled_count
    type: count
    value_format: '#,##0'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
  - measure: total_net_payout
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${customer_payout}
  - measure: total_authorized_amount
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${authorized_amount}
  - measure: total_settled_amount
    type: sum
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${authorized_amount}
  - measure: avg_settled_amount
    type: avg
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${authorized_amount}
  - measure: max_settled_amount
    type: max
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${authorized_amount}
  - measure: min_settled_amount
    type: min
    value_format: '$#,##0.00'
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${authorized_amount}
  - measure: unique_payers
    type: count_distinct
    drill_fields:
    - detail*
    filters:
    - is_settled: True
    sql: ${full_name}