label: "Statement Processing"
connection: "statement_db"
include: "stmt_db_*.view"

explore: stmt_db_dig_sales_analytics {
  label: "Statement DB"
  hidden: yes
  sql_always_where: {% if stmt_db_period_id.statement_name_filter._is_filtered %}
  ${stmt_db_period_id.statement_date} < {% date_end stmt_db_period_id.statement_name_filter %} AND
${stmt_db_period_id.statement_date} > DATE_ADD({% date_start stmt_db_period_id.statement_name_filter %}, interval -12 month)
{% else %}
1 = 1
{% endif %}
;;


  join: dig_sales {
    sql_on: ${dig_sales.actual_statement_no} = ${stmt_db_dig_sales_analytics.actual_statement_no} ;;
    relationship: many_to_one
  }

  join: stmt_db_dig_sales_period_id {
    sql_on: ${dig_sales.statement_id} = ${stmt_db_dig_sales_period_id.statement_id} ;;
    relationship: many_to_one
  }

  join: stmt_db_period_id {
    sql_on: ${stmt_db_dig_sales_period_id.period_id} = ${stmt_db_period_id.period_id} ;;
    relationship: many_to_one
  }

  join: stmt_db_trans_type {
    type: inner
    sql_on: ${stmt_db_trans_type.trans_type} = ${stmt_db_dig_sales_analytics.trans_type} ;;
    relationship: many_to_one
    view_label: "Transaction Type"
  }

  join: stmt_db_country {
    type: left_outer
    sql_on: ${stmt_db_dig_sales_analytics.country_id} = ${stmt_db_country.id} ;;
    relationship: many_to_one
    view_label: "Territory"
  }

  join: stmt_db_customer_master_master {
    type: left_outer
    sql_on: ${stmt_db_dig_sales_analytics.customer_master_master_id} = ${stmt_db_customer_master_master.customer_master_master_id} ;;
    relationship: many_to_one
  }

  join: stmt_db_collections_sub_transactions {
    type: left_outer
    sql_on: ${stmt_db_dig_sales_analytics.actual_statement_no} LIKE ${stmt_db_collections_sub_transactions.grouping} ;;
    relationship: many_to_one
    view_label: "Statement"
  }

  join: stmt_db_collections_substores {
    type: left_outer
    sql_on: ${stmt_db_dig_sales_analytics.actual_statement_no} LIKE ${stmt_db_collections_substores.grouping} ;;
    relationship: many_to_one
    view_label: "Statement"
  }

# join: stmt_db_pl_storelist {
#    type: left_outer
#    sql_on: ${stmt_db_pl_storelist.cmm_id} = ${stmt_db_customer_master_master.customer_master_master_id} ;;
#    relationship: many_to_one
#    view_label: "PL"
#    }

#  join: stmt_db_pl_categories {
#    type: inner
#    sql_on: ${stmt_db_pl_storelist.category_id} = ${stmt_db_pl_categories.category_id} ;;
#    relationship: many_to_one
#    view_label: "PL"
#  }
}

include: "dt_itunes_complete_month_by_service.view"
explore: dt_itunes_complete_month_by_service {
  label: "iTunes Complete Month by Service"
  hidden: yes
}

explore: stmt_db_pl_storelist {
  label: "Report Ingestion List"
  #hidden: yes
  always_filter: {
    filters: [stmt_db_pl_categories.category_name: "-Dead Stores, -Merged Retailers, -Non-DMS Processes"
      ]#, stmt_db_pl_cells.date_month: "6 months ago for 6 months"

     # stmt_db_pl_cells.date_month: ">= DATE_ADD(TIMESTAMP(DATE_FORMAT(CURDATE(),'%Y-%m-01')),INTERVAL -6 month)",
    #  stmt_db_pl_cells.date_month: "< DATE_ADD(DATE_ADD(TIMESTAMP(DATE_FORMAT(CURDATE(),'%Y-%m-01')),INTERVAL -6 month),INTERVAL 6 month)"]
  }


  join: stmt_db_pl_categories {
    type: inner
    sql_on: ${stmt_db_pl_storelist.category_id} = ${stmt_db_pl_categories.category_id} ;;
    relationship: many_to_one
    #view_label: "PL"
  }

  join: stmt_db_customer_master_master {
    type: left_outer
    sql_on: ${stmt_db_pl_storelist.cmm_id} = ${stmt_db_customer_master_master.customer_master_master_id} ;;
    relationship: many_to_one
    #view_label: "PL"
  }

  join: stmt_db_pl_cells {
    type: inner
    sql_on: ${stmt_db_pl_storelist.list_id} = ${stmt_db_pl_cells.list_id} ;;
    relationship: many_to_one
    #view_label: "PL"
  }
}