Is Order Items

view: is_order_items {
  sql_table_name: Company_OrderItems ;;

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

  dimension: cpu {
    type: number
    sql: ${TABLE}.CPU ;;
  }

  dimension: item_description {
    sql: ${TABLE}.ItemDescription ;;
  }

  dimension: item_name {
    sql: ${TABLE}.ItemName ;;
  }

  dimension: item_type {
    type: int
    sql: ${TABLE}.ItemType ;;
  }

  dimension: notes {
    sql: ${TABLE}.Notes ;;
  }

  dimension: order_id {
    type: int
    sql: ${TABLE}.OrderId ;;
  }

  dimension: ppu {
    type: number
    value_format: "$#,##0"
    sql: ${TABLE}.PPU ;;
  }

  dimension: product_id {
    type: int
    sql: ${TABLE}.ProductId ;;
  }

  dimension: qty {
    type: int
    sql: ${TABLE}.Qty ;;
  }

  dimension: subscription_plan_id {
    type: int
    sql: ${TABLE}.SubscriptionPlanId ;;
  }

  dimension: unit_size_string {
    description: "This need to be done with product_id as the SKU is not accessible (Company_Products)"
    hidden: yes
  }

  dimension: unit_size {
    type: number
    sql: ${unit_size_string} + 0 ;;
  }

  dimension: is_subscription {
    type: yesno
    sql: ${item_type} = 9 ;;
  }

  dimension: item_category {
  }

  dimension: coupon_code {
    sql: if(${item_category}='SPECIAL', UPPER(${notes}), NULL) ;;
  }

  measure: total_unit_qty {
    type: sum
    sql: (${unit_size}+0) * ${qty} ;;
  }

  measure: total_qty {
    type: sum
    sql: ${qty} ;;
  }

  measure: count {
    type: count
    drill_fields: [id, item_name, orders.infusionsoft_order_id, products.id, products.name]
  }

  measure: total_discount {
    type: sum
    sql: IF(${item_name}='Special' OR ${item_name}='Special Discount' OR ${item_type}=7, ${ppu} * ${qty}, 0) ;;
  }

  measure: total_shipping {
    type: sum
    sql: IF(${product_id}= 'Product_ShippingID' OR ${item_type} IN (1, 14), ${ppu} * ${qty}, 0) ;;
  }

  measure: total_tax {
    type: sum
    sql: IF(${item_name}='Tax' OR ${item_type}=2, ${ppu} * ${qty}, 0) ;;
  }

  measure: total_amount {
    type: sum
    value_format: "$#,###"
    sql: IF(${product_id}=Product_DiscountID AND ${ppu} < -397, -397, ${ppu}) * ${qty} ;;
  }
}

- view: is_order_items
  sql_table_name: Company_OrderItems
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.Id
  - dimension: cpu
    type: number
    sql: ${TABLE}.CPU
  - dimension: item_description
    sql: ${TABLE}.ItemDescription
  - dimension: item_name
    sql: ${TABLE}.ItemName
  - dimension: item_type
    type: int
    sql: ${TABLE}.ItemType
  - dimension: notes
    sql: ${TABLE}.Notes
  - dimension: order_id
    type: int
    sql: ${TABLE}.OrderId
  - dimension: ppu
    type: number
    value_format: '$#,##0'
    sql: ${TABLE}.PPU
  - dimension: product_id
    type: int
    sql: ${TABLE}.ProductId
  - dimension: qty
    type: int
    sql: ${TABLE}.Qty
  - dimension: subscription_plan_id
    type: int
    sql: ${TABLE}.SubscriptionPlanId
  - dimension: unit_size_string
    hidden: true
    description: This need to be done with product_id as the SKU is not accessible (Company_Products)
  - dimension: unit_size
    type: number
    sql: ${unit_size_string} + 0
  - dimension: is_subscription
    type: yesno
    sql: ${item_type} = 9
  - dimension: item_category
  - dimension: coupon_code
    sql: if(${item_category}='SPECIAL', UPPER(${notes}), NULL)
  - measure: total_unit_qty
    type: sum
    sql: (${unit_size}+0) * ${qty}
  - measure: total_qty
    type: sum
    sql: ${qty}
  - measure: count
    type: count
    drill_fields:
    - id
    - item_name
    - orders.infusionsoft_order_id
    - products.id
    - products.name
  - measure: total_discount
    type: sum
    sql: IF(${item_name}='Special' OR ${item_name}='Special Discount' OR ${item_type}=7, ${ppu} * ${qty}, 0)
  - measure: total_shipping
    type: sum
    sql: IF(${product_id}= 'Product_ShippingID' OR ${item_type} IN (1, 14), ${ppu} * ${qty}, 0)
  - measure: total_tax
    type: sum
    sql: IF(${item_name}='Tax' OR ${item_type}=2, ${ppu} * ${qty}, 0)
  - measure: total_amount
    type: sum
    value_format: '$#,###'
    sql: IF(${product_id}=Product_DiscountID AND ${ppu} < -397, -397, ${ppu}) * ${qty}