Class: ViewCustomer

Inherits:
ApplicationViewRecord show all
Includes:
Models::SearchableView
Defined in:
app/models/view_customer.rb

Overview

== Schema Information

Table name: view_customers
Database name: primary

id(FK → parties.id for the customer.) :integer primary key
affiliations(Customer affiliation tags (text field).) :text is an Array
buying_group_name(Name of the buying group.) :string(255)
catalog_name(Name of the customer's assigned catalog.) :string(255)
creation_method(How the customer record was created (e.g. manual, import, online).) :integer
customer_first_contact_email(Email of the first active contact person under this customer.) :string(255)
customer_main_email(Primary email address from contact_points.) :string(255)
customer_main_fax(Primary fax number from contact_points.) :string(255)
customer_main_phone(Primary phone or cell number from contact_points.) :string(255)
customer_since(Date when the customer relationship began.) :date
data_import_name(Name of the data import batch.) :string(255)
drop_reasons(Array of drop reason strings formatted as 'reason_code by rep_name on date', ordered newest first.) :text is an Array
email_unsubscribed(Whether the primary email has opted out of any email category (reviews, promotions, newsletters, or announcements).) :boolean
first_name(First name (only populated for individual/homeowner profiles, profile_id = 35).) :string
first_order_date(Date of the very first order placed.) :date
floor_types_interest_last_2_years(Array of floor_type_id values from opportunities created in the last 2 years.) :integer is an Array
full_name(Customer's full display name (company name or person name).) :string(255)
has_countertop_display(Whether the customer has a countertop heating product display (from locator_records).) :boolean
has_in_floor_display(Whether the customer has an in-floor heating product display (from locator_records).) :boolean
has_install_capability(Whether the customer has product installation capability (from profiling).) :boolean
has_online_account(Whether the customer or any contact has a web account.) :boolean
has_sales_capability(Whether the customer has sales capability (from profiling).) :boolean
has_service_capability(Whether the customer has service capability (from profiling).) :boolean
is_customer_only(Whether this is a customer-only account (no prospect/lead history).) :boolean
largest_sales_opportunity_value(Highest single sales opportunity value (monetary, USD). 0 if none.) :decimal(, )
largest_sales_order_total(Highest single invoiced order total (monetary, USD). 0 if none.) :decimal(, )
last_name(Last name (only populated for individual/homeowner profiles, profile_id = 35).) :string
last_order_date(Date of the most recent order.) :date
last_quote_date(Date of the most recent opportunity (labeled as last_quote_date).) :date
last_sales_activity_age_in_month(Months since last completed sales call (-1 if no activity exists). Integer, floored.) :integer
last_sales_activity_datetime(Timestamp of the most recent completed sales call activity.) :datetime
lifetime_number_of_opportunities(Total number of opportunities ever created. 0 if none.) :integer
lifetime_number_of_orders(Total number of orders ever placed. 0 if none.) :integer
lifetime_revenue(Total lifetime revenue (monetary, USD). 0 if none.) :decimal(, )
local_rep_full_name(Full name of the local sales rep.) :string(255)
local_rep_inactive(True when local rep is inactive.) :boolean
main_address_city(City from the customer's primary address.) :string(255)
main_address_country_iso3(ISO 3166-1 alpha-3 country code (e.g. USA, CAN).) :string(3)
main_address_state_code(State/province code from the customer's primary address.) :string(20)
main_address_zip(ZIP/postal code from the customer's primary address.) :string(255)
middle_name(Middle name (only populated for individual/homeowner profiles, profile_id = 35).) :string
next_anniversary_date(Next customer anniversary date for retention outreach.) :date
number_of_efh_jobs_per_year_range(Self-reported electric floor heating jobs per year (range string from profiling).) :string(255)
number_of_employees(Number of employees at the customer's organization.) :integer
number_of_locations_range(Self-reported number of business locations (range string from profiling).) :string(255)
number_of_total_jobs_per_year_range(Self-reported total jobs per year (range string from profiling).) :string(255)
number_of_wy_sales_per_year_range(Self-reported WarmlyYours sales per year (range string from profiling).) :string(255)
open_sales_activity(Whether the customer has an open (uncompleted) sales activity.) :boolean
pricing_program_name(Title of the Tier 2 pricing program coupon (if assigned).) :text
primary_rep_full_name(Full name of the primary sales rep.) :string(255)
primary_rep_inactive(True when primary rep is inactive; useful for filtering.) :boolean
profile_name(Name of the customer profile type.) :string(255)
profiling_data_last_edited_at(Timestamp when the customer's profiling data was last updated.) :datetime
rating(Customer rating value (internal scoring).) :string(255)
report_grouping(Sales channel grouping. Falls back through customer → buying group → profile → 'Homeowners'.) :string
room_types_interest_last_2_years(Array of room_type_id values from opportunities created in the last 2 years.) :integer is an Array
sales_priority_index(Sales Priority Index (SPI) — numeric score for sales prioritization. 0 if not set.) :integer
secondary_rep_full_name(Full name of the secondary sales rep.) :string(255)
secondary_rep_inactive(True when secondary rep is inactive.) :boolean
service_rep_full_name(Full name of the service rep.) :string(255)
service_rep_inactive(True when service rep is inactive.) :boolean
source_full_name(Full name of the lead source.) :string(500)
state(Customer lifecycle state (guest, lead_qualify, lead, prospect, customer, closed, bankrupt). Exclude guest for most analytics.) :string(255)
store_name(Name of the catalog's store.) :string(255)
trailing_twelve_months_conversion_rate(Opportunity-to-order conversion rate over the last 12 months (decimal).) :decimal(5, 2)
trailing_twelve_months_number_of_opportunities(Number of opportunities created in the last 12 months.) :integer
trailing_twelve_months_number_of_orders(Number of orders placed in the last 12 months. 0 if none.) :integer
trailing_twelve_months_revenue(Total revenue in the last 12 months (monetary, USD). 0 if none.) :decimal(, )
watch(Boolean flag indicating whether the customer is on the sales watch list.) :integer
created_at(Customer record creation timestamp.) :datetime
updated_at(Customer record last update timestamp.) :datetime
buying_group_id(FK → buying_groups.id if the customer belongs to a buying group.) :integer
catalog_id(FK → catalogs.id for the customer's assigned catalog.) :integer
creator_id(FK → parties.id for the user who created this customer.) :integer
data_import_id(FK → data_imports.id if the customer was created via data import.) :integer
local_sales_rep_id(FK → parties.id for the local/territory sales rep.) :integer
main_address_id(FK → addresses.id — primary address (shipping > billing > mailing fallback).) :integer
primary_sales_rep_id(FK → parties.id for the primary sales rep.) :integer
profile_id(FK → profiles.id — customer profile type (e.g. 35 = individual/homeowner).) :integer
secondary_sales_rep_id(FK → parties.id for the secondary sales rep.) :integer
service_rep_id(FK → parties.id for the assigned service rep.) :integer
source_id(FK → sources.id — lead source for the customer.) :integer
store_id(FK → stores.id for the catalog's store.) :integer
tier2_program_pricing_id(FK → coupons.id for the Tier 2 pricing program coupon.) :integer

Constant Summary collapse

HAS_TOPIC_RESPONSE_SQL = 
<<-EOS
EXISTS(
select 1 from party_topics pt
inner join party_topics_topic_responses pttr on pttr.customer_topic_id = pt.id
where (
pt.party_id = view_customers.id or
pt.party_id IN (select cnt.id from parties cnt where cnt.customer_id = view_customers.id)
)
and
pttr.topic_response_id IN (?)
)
EOS

Has many collapse

Class Method Summary collapse

Instance Method Summary collapse

Methods included from Models::SearchableView

#crm_link, #crm_link_subtitle, #has_columns?, #main_resource, #readonly?

Methods inherited from ApplicationViewRecord

create, create!, #readonly?

Methods inherited from ApplicationRecord

ransackable_associations, ransackable_attributes, ransortable_attributes, #to_relation

Methods included from Models::EventPublishable

#publish_event

Class Method Details

.contact_point_category_cont ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are contact point category cont. Active Record Scope

Returns:

See Also:



132
 
# File 'app/models/view_customer.rb', line 132

scope :contact_point_category_cont, ->(*categories) { where("EXISTS(select 1 from contact_points cp where cp.party_id = view_customers.id and cp.category IN (?))", categories.flatten) }

.contact_point_cont ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are contact point cont. Active Record Scope

Returns:

See Also:



131
 
# File 'app/models/view_customer.rb', line 131

scope :contact_point_cont, ->(detail) { where("EXISTS(select 1 from contact_points cp where cp.party_id = view_customers.id and cp.detail ilike ?)", "%#{detail}%") }

.dealer_record_state ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are dealer record state. Active Record Scope

Returns:

See Also:



121
122
123
124
125
126
127
128
129
130
 
# File 'app/models/view_customer.rb', line 121

scope :dealer_record_state, ->(state_list_in) {
  state_list = state_list_in.dup
  conditions = []
  conditions << "(lr.is_inactive IS TRUE)" if state_list.delete('Blacklisted')
  if state_list.present?
    state_list_condition = state_list.map{|s| "'#{s}'"}.join(',')
    conditions << "(lr.state IN (#{state_list_condition}))"
  end
  where("EXISTS(select 1 from locator_records lr where lr.customer_id = view_customers.id and (#{conditions.join(' or ')}))")
}

.has_an_activity_of_type ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are has an activity of type. Active Record Scope

Returns:

See Also:



133
 
# File 'app/models/view_customer.rb', line 133

scope :has_an_activity_of_type, ->(activity_type_ids) { where("EXISTS(select 1 from activities where activities.party_id = view_customers.id and activities.activity_type_id IN (?))", activity_type_ids)}

.has_an_open_activity_of_type ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are has an open activity of type. Active Record Scope

Returns:

See Also:



134
 
# File 'app/models/view_customer.rb', line 134

scope :has_an_open_activity_of_type, ->(activity_type_ids) { where("EXISTS(select 1 from activities where activities.party_id = view_customers.id and activities.activity_result_type_id is null and activities.activity_type_id IN (?))", activity_type_ids)}

.has_one_of_these_topic_responses ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are has one of these topic responses. Active Record Scope

Returns:

See Also:



135
 
# File 'app/models/view_customer.rb', line 135

scope :has_one_of_these_topic_responses, ->(topic_response_ids) { where(HAS_TOPIC_RESPONSE_SQL, [topic_response_ids].flatten.map(&:to_i)) }

.jde_abn_eq ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are jde abn eq. Active Record Scope

Returns:

See Also:



112
 
# File 'app/models/view_customer.rb', line 112

scope :jde_abn_eq, ->(abn) { where("EXISTS(SELECT 1 FROM addresses ja WHERE ja.party_id = c.id AND ja.jde_abn = ?)", abn)}

.number_of_offices ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are number of offices. Active Record Scope

Returns:

See Also:



144
145
146
147
148
149
150
151
 
# File 'app/models/view_customer.rb', line 144

scope :number_of_offices, ->(number_of_offices) {
  if number_of_offices == 'unknown'
    conditions = "parties.number_of_offices is null"
  else
    conditions = "parties.number_of_offices = '#{number_of_offices}'"
  end
  where("EXISTS(select 1 from parties where parties.id = view_customers.id and #{conditions})")
}

.projects_per_year ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are projects per year. Active Record Scope

Returns:

See Also:



136
137
138
139
140
141
142
143
 
# File 'app/models/view_customer.rb', line 136

scope :projects_per_year, ->(projects_per_year) {
  if projects_per_year == 'unknown'
    conditions = "parties.projects_per_year is null"
  else
    conditions = "parties.projects_per_year = '#{projects_per_year}'"
  end
  where("EXISTS(select 1 from parties where parties.id = view_customers.id and #{conditions})")
}

.ransackable_scopes(_auth_object = nil) ⇒ Object 



153
154
155
156
157
 
# File 'app/models/view_customer.rb', line 153

def self.ransackable_scopes(_auth_object = nil)
  super + %i[contact_point_cont contact_point_category_cont source_id watch dealer_record_state jde_abn_eq
             has_an_activity_of_type has_an_open_activity_of_type
             has_one_of_these_topic_responses projects_per_year number_of_offices]
end

.reset ⇒ Object

Convenience override, since viewopportunity depends on this view



172
173
174
175
176
 
# File 'app/models/view_customer.rb', line 172

def self.reset
  ViewOpportunity.drop_view
  super
  ViewOpportunity.reset
end

.source_id ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are source id. Active Record Scope

Returns:

See Also:



113
114
115
116
117
118
 
# File 'app/models/view_customer.rb', line 113

scope :source_id, ->(*sids) {
  sids = sids.flatten.compact
  next all if sids.blank?

  where(source_id: Source.self_and_descendants_ids(sids))
}

.watch ⇒ ActiveRecord::Relation<ViewCustomer>

A relation of ViewCustomers that are watch. Active Record Scope

Returns:

See Also:



119
 
# File 'app/models/view_customer.rb', line 119

scope :watch, ->(value) { ['Y','1','T'].include?(value.to_s.first.upcase) ? where("watch IS NOT NULL and watch >= 2") : where("watch IS NULL or watch = 1") }

Instance Method Details

#certifications ⇒ ActiveRecord::Relation<Certification>

Returns:

See Also:



110
 
# File 'app/models/view_customer.rb', line 110

has_many :certifications, foreign_key: :party_id

#customer_drop_events ⇒ ActiveRecord::Relation<CustomerDropEvent>

Returns:

See Also:



109
 
# File 'app/models/view_customer.rb', line 109

has_many :customer_drop_events, foreign_key: :customer_id

#result_class ⇒ Object 



159
160
161
 
# File 'app/models/view_customer.rb', line 159

def result_class
  report_grouping == 'Homeowners' ? 'Homeowner' : 'Organization'
end

#result_subtitle ⇒ Object 



167
168
169
 
# File 'app/models/view_customer.rb', line 167

def result_subtitle
  "#{main_address_city}, #{main_address_state_code}"
end

#result_title ⇒ Object 



163
164
165
 
# File 'app/models/view_customer.rb', line 163

def result_title
  "#{full_name} [#{id}]"
end