Class: Report::WebSitePerformance::WebSitePerformance

Inherits:

Object

• Object
• Report::WebSitePerformance::WebSitePerformance

Defined in:

app/services/report/web_site_performance/web_site_performance.rb

Defined Under Namespace

Classes: Result

Class Method Summary

• .breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object
• .classification_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object
• .current_vs_previous_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object
• .dates_to_check(start_date, end_date, compare_start_date, compare_end_date) ⇒ Object
• .get_data_convertions(start_date, end_date) ⇒ Object
• .get_data_for_charts(leads_cur) ⇒ Object
• .get_data_for_charts2(data, result_field, date_range, type_breakdown) ⇒ Object
• .get_leads(start_date, end_date) ⇒ Object
• .get_values_lifecycle_over_time(leads_cur, leads_prev) ⇒ Object
• .metrics_trend_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object
• .orders_by_origin(dates, branch) ⇒ Object
• .orders_classification(dates, branch) ⇒ Object
• .orders_classification_graph(dates, branch) ⇒ Object
• .orders_current_vs_previous(dates, type_data, branch) ⇒ Object
• .orders_metrics(dates, branch) ⇒ Object
• .orders_metrics_trend(dates, branch) ⇒ Object
• .visits_by_region(dates, branch) ⇒ Object
• .visits_current_vs_previous(dates, code, branch) ⇒ Object
• .visits_graphs(dates, code, branch) ⇒ Object
• .visits_values(dates, branch) ⇒ Object
• .web_site_performance_result(options = {}) ⇒ Object

Class Method Details

.breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 321

def self.breakdown_by_week_or_month(values = [], type_breakdown)
  week = []
  month = []
  values.each do |v|
    week << v[:date].strftime('%V, %y')
    month << (Date::ABBR_MONTHNAMES[v[:date].strftime('%m').to_i] + v[:date].strftime(', %y'))
  end
  week = week.uniq
  month = month.uniq

  date = ''
  value = 0
  if type_breakdown == 2
    result = []
    week.each do |w|
      values.each do |v|
        date = 'W' + w.to_s
        value += v[:values] if v[:date].strftime('%V, %y') == w
      end
      result << { date: date, values: value }
      date = ''
      value = 0
    end
  else
    result = []
    month.each do |m|
      values.each do |v|
        date = m
        value += v[:values] if (Date::ABBR_MONTHNAMES[v[:date].strftime('%m').to_i] + v[:date].strftime(', %y')) == m
      end
      result << { date: date, values: value }
      date = ''
      value = 0
    end
  end

  result
end

.classification_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 248

def self.classification_breakdown_by_week_or_month(values = [], type_breakdown)
  week = []
  month = []
  values.each do |ord|
    week << ord[:date].strftime('%V, %y') if ord[:id] == 2
    month << (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) if ord[:id] == 2
  end
  week = week.uniq
  month = month.uniq

  if type_breakdown == 2
    result = []
    date = ''
    income1 = 0
    income2 = 0
    income3 = 0
    income4 = 0
    income5 = 0
    week.each do |w|
      values.each do |ord|
        date = 'W' + w.to_s
        income1 += ord[:value] if ord[:date].strftime('%V, %y') == w && ord[:id] == 1
        income2 += ord[:value] if ord[:date].strftime('%V, %y') == w && ord[:id] == 2
        income3 += ord[:value] if ord[:date].strftime('%V, %y') == w && ord[:id] == 3
        income4 += ord[:value] if ord[:date].strftime('%V, %y') == w && ord[:id] == 4
        income5 += ord[:value] if ord[:date].strftime('%V, %y') == w && ord[:id] == 5
      end
      result << { id: 1, date: date, value: income1 }
      result << { id: 2, date: date, value: income2 }
      result << { id: 3, date: date, value: income3 }
      result << { id: 4, date: date, value: income4 }
      result << { id: 5, date: date, value: income5 }
      date = ''
      income1 = 0
      income2 = 0
      income3 = 0
      income4 = 0
      income5 = 0
    end
  else
    result = []
    date = ''
    income1 = 0
    income2 = 0
    income3 = 0
    income4 = 0
    income5 = 0
    month.each do |m|
      values.each do |ord|
        date = m
        income1 += ord[:value] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m && ord[:id] == 1
        income2 += ord[:value] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m && ord[:id] == 2
        income3 += ord[:value] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m && ord[:id] == 3
        income4 += ord[:value] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m && ord[:id] == 4
        income5 += ord[:value] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m && ord[:id] == 5
      end
      result << { id: 1, date: date, value: income1 }
      result << { id: 2, date: date, value: income2 }
      result << { id: 3, date: date, value: income3 }
      result << { id: 4, date: date, value: income4 }
      result << { id: 5, date: date, value: income5 }
      date = ''
      income1 = 0
      income2 = 0
      income3 = 0
      income4 = 0
      income5 = 0
    end
  end

  result
end

.current_vs_previous_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 185

def self.current_vs_previous_breakdown_by_week_or_month(values = [], type_breakdown)
  current_week = []
  previous_week = []
  current_month = []
  previous_month = []
  values.each do |ord|
    current_week << ord[:date_current].strftime('%V, %y')
    previous_week << ord[:date_previous].strftime('%V, %y')
    current_month << (Date::ABBR_MONTHNAMES[ord[:date_current].strftime('%m').to_i] + ord[:date_current].strftime(', %y'))
    previous_month << (Date::ABBR_MONTHNAMES[ord[:date_previous].strftime('%m').to_i] + ord[:date_previous].strftime(', %y'))
  end
  current_week = current_week.uniq
  previous_week = previous_week.uniq
  current_month = current_month.uniq
  previous_month = previous_month.uniq

  if type_breakdown == 2
    result = []
    date_current = ''
    date_previous = ''
    current_value = 0
    previous_value = 0
    i = 0
    current_week.each do |w|
      values.each do |ord|
        date_current = 'W' + w.to_s
        date_previous = 'W' + previous_week[i].to_s
        current_value += ord[:current] if ord[:date_current].strftime('%V, %y') == w
        previous_value += ord[:previous] if ord[:date_current].strftime('%V, %y') == w
      end
      result << { date_current: date_current, date_previous: date_previous, current: current_value, previous: previous_value }
      date_current = ''
      date_previous = ''
      current_value = 0
      previous_value = 0
      i += 1
    end
  else
    result = []
    date_current = ''
    date_previous = ''
    current_value = 0
    previous_value = 0
    i = 0
    current_month.each do |m|
      values.each do |ord|
        date_current = m
        date_previous = previous_month[i]
        current_value += ord[:current] if (Date::ABBR_MONTHNAMES[ord[:date_current].strftime('%m').to_i] + ord[:date_current].strftime(', %y')) == m
        previous_value += ord[:previous] if (Date::ABBR_MONTHNAMES[ord[:date_current].strftime('%m').to_i] + ord[:date_current].strftime(', %y')) == m
      end
      result << { date_current: date_current, date_previous: date_previous, current: current_value, previous: previous_value }
      date_current = ''
      date_previous = ''
      current_value = 0
      previous_value = 0
      i += 1
    end
  end

  result
end

.dates_to_check(start_date, end_date, compare_start_date, compare_end_date) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 129

def self.dates_to_check(start_date, end_date, compare_start_date, compare_end_date)
  date_from = start_date
  date_to = end_date
  date_from_prev = compare_start_date
  date_to_prev = compare_end_date

  { date_from: date_from, date_to: date_to, date_from_prev: date_from_prev, date_to_prev: date_to_prev }
end

.get_data_convertions(start_date, end_date) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 475

def self.get_data_convertions(start_date, end_date)
  sql = <<-SQL
    with leads as (
        select distinct id as party_id,timezone('America/Chicago', timestamptz(created_at))::date as created_at,state,source_id
        from parties
        where type = 'Customer'
        and state in ('lead','prospect','customer','lead_qualify')
        and timezone('America/Chicago', timestamptz(created_at))::date between '#{start_date}' and '#{end_date}'
        and creation_method = 1
    )
    select distinct l.state,party_id,opportunity_id,vof.date as opp_created_date,opp_state,vof.opp_won_lost_date as won_lost_date,consolidated_value as opp_consolidated_value,vof.order_id,voo.order_id,revenue_consolidated as revenue,voo.ord_date as date
    from leads l
    left join view_opportunities_facts vof on vof.customer_id = l.party_id
    left join view_orders_origin_facts voo on voo.customer_id = l.party_id
  SQL

  data = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }
  result = {}
  result[:total_leads] = data.map { |r| r[:party_id] }.uniq.count
  result[:won_opp] = data.map { |r| r[:opp_state] == 'won' ? 1 : 0 }.sum
  result[:won_revenue] = data.map { |r| r[:opp_state] == 'won' ? r[:revenue].to_f : 0 }.sum
  result[:orders] = data.map { |r| r[:state] == 'customer' ? r[:party_id] : nil }.compact.uniq.count
  result[:orders_revenue] = data.map { |r| r[:order_id].nil? ? nil : { order_id: r[:order_id], revenue: r[:revenue] } }.compact.uniq.map { |s| s[:revenue].to_f }.sum
  result[:interest] = data.map { |r| r[:opp_state] == 'interest' ? 1 : 0 }.sum
  result[:interest_revenue] = data.map { |r| r[:opp_state] == 'interest' ? r[:opp_consolidated_value].to_f : 0 }.sum
  result[:promised] = data.map { |r| r[:opp_state] == 'promised' ? 1 : 0 }.sum
  result[:promised_revenue] = data.map { |r| r[:opp_state] == 'promised' ? r[:opp_consolidated_value].to_f : 0 }.sum
  result[:follow_up] = data.map { |r| r[:opp_state] == 'follow_up' ? 1 : 0 }.sum
  result[:follow_up_revenue] = data.map { |r| r[:opp_state] == 'follow_up' ? r[:opp_consolidated_value].to_f : 0 }.sum
  result[:qualify] = data.map { |r| r[:opp_state] == 'qualify' ? 1 : 0 }.sum
  result[:qualify_revenue] = data.map { |r| r[:opp_state] == 'qualify' ? r[:opp_consolidated_value].to_f : 0 }.sum
  result[:quoting] = data.map { |r| %w[quoting instant_quoting].include?(r[:opp_state]) ? 1 : 0 }.sum
  result[:quoting_revenue] = data.map { |r| %w[quoting instant_quoting].include?(r[:opp_state]) ? r[:opp_consolidated_value].to_f : 0 }.sum
  result[:lost] = data.map { |r| %w[lost cancelled abandoned untracked].include?(r[:opp_state]) ? 1 : 0 }.sum
  result[:lost_revenue] = data.map { |r| %w[lost cancelled abandoned untracked].include?(r[:opp_state]) ? r[:opp_consolidated_value].to_f : 0 }.sum

  result
end

.get_data_for_charts(leads_cur) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 434

def self.get_data_for_charts(leads_cur)
  leads_behavior_chart = {}
  dates = []
  leads_cur.map { |r| dates << r[:created_at].to_date }
  dates = dates.uniq.sort!
  dates.each do |d|
    leads_behavior_chart[d] = { leads: 0, prospect: 0, customer: 0 }
    leads_cur.each do |l|
      next unless d == l[:created_at].to_date

      leads_behavior_chart[d][:leads] += 1
      leads_behavior_chart[d][:prospect] += l[:to_prospect]
      leads_behavior_chart[d][:customer] += l[:to_customer]
    end
  end

  leads_behavior_chart
end

.get_data_for_charts2(data, result_field, date_range, type_breakdown) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 453

def self.get_data_for_charts2(data, result_field, date_range, type_breakdown)
  result_field == 'to_lead_qualify' ? result_field = 'coalesce(to_lead_qualify,to_lead)' : result_field
  week = 'extract(week from created_at)::int'
  month = "to_char(created_at,'Mon, YY')::varchar"
  year = "to_char(created_at,'YY')::int"
  result = {}
  if type_breakdown == 1
    data_final = data.where(created_at: date_range).group(:created_at).order(:created_at).sum(result_field)
    dates = date_range.map { |r| r }
    dates.each { |r| result[r] = data_final[r] }
  elsif type_breakdown == 2
    data_final = data.where(created_at: date_range).group(week, year).order(year, week).sum(result_field)
    dates = date_range.map { |r| [r.strftime('%W').to_i, r.strftime('%y').to_i] }.uniq
    dates.each { |r| result[[r[0], r[1]]] = data_final[[r[0], r[1]]] if r[0].positive? }
  elsif type_breakdown == 3
    data_final = data.where(created_at: date_range).group(month, year).order(year, month).sum(result_field)
    dates = date_range.map { |r| [r.strftime('%b').to_s, r.strftime('%y').to_i] }.uniq
    dates.each { |r| result[[r[0], r[1]]] = data_final[[r[0] + ', ' + r[1].to_s, r[1]]] }
  end
  result
end

.get_leads(start_date, end_date) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 360

def self.get_leads(start_date, end_date)
  sql_leads = <<-SQL
    select distinct id,timezone('America/Chicago', timestamptz(created_at))::date as created_at,state,source_id,
    null::date as date_of_change_pt,0::int as to_prospect,null::date as date_of_change_cr,0::int as to_customer
    from parties
    where type = 'Customer'
    and state in ('lead','prospect','customer','lead_qualify')
    and timezone('America/Chicago', timestamptz(created_at))::date between '#{start_date}' and '#{end_date}'
    and creation_method = 1
  SQL

  leads = ActiveRecord::Base.connection.execute(sql_leads).to_a.map { |r| r.symbolize_keys }

  sql_to_prospect = <<-SQL
    select max(timezone('America/Chicago', timestamptz(created_at))::date) as date_pt,item_id,1::int as to_prospect
    from versions
    where item_type = 'Party'
    and item_id in (#{leads.map { |r| r[:id] }.join(',')})
    and object_changes #>> '{"state",1}' = 'prospect'
    group by item_id
  SQL

  leads_to_prospect = RecordVersion.connection.execute(sql_to_prospect).to_a.map { |r| r.symbolize_keys }

  sql_to_customer = <<-SQL
    select max(timezone('America/Chicago', timestamptz(created_at))::date) as date_cr,item_id,1::int as to_customer
    from versions
    where item_type = 'Party'
    and item_id in (#{leads.map { |r| r[:id] }.join(',')})
    and object_changes #>> '{"state",1}' = 'customer'
    group by item_id
  SQL

  leads_to_customer = RecordVersion.connection.execute(sql_to_customer).to_a.map { |r| r.symbolize_keys }

  leads.each do |l|
    leads_to_prospect.each do |lp|
      if l[:id] == lp[:item_id]
        l[:date_of_change_pt] = lp[:date_pt]
        l[:to_prospect] = lp[:to_prospect]
      end
    end

    leads_to_customer.each do |lc|
      if l[:id] == lc[:item_id]
        l[:date_of_change_cr] = lc[:date_cr]
        l[:to_customer] = lc[:to_customer]
      end
    end
  end

  leads
end

.get_values_lifecycle_over_time(leads_cur, leads_prev) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 414

def self.get_values_lifecycle_over_time(leads_cur, leads_prev)
  leads_behavior = {}
  leads_behavior[:leads] = { current: 0, previous: 0, var: 0 }
  leads_behavior[:leads][:current] = leads_cur.count
  leads_behavior[:leads][:previous] = leads_prev.count
  leads_behavior[:leads][:var] = leads_behavior[:leads][:previous].zero? ? 0 : (((leads_behavior[:leads][:current].to_f - leads_behavior[:leads][:previous].to_f) / leads_behavior[:leads][:previous].to_f) * 100).round(2)
  leads_behavior[:leads_to_prospect] = { current: 0, previous: 0, var: 0 }
  leads_behavior[:leads_to_prospect][:current] = leads_cur.map { |r| r[:to_prospect] }.sum
  leads_behavior[:leads_to_prospect][:previous] = leads_prev.map { |r| r[:to_prospect] }.sum
  leads_behavior[:leads_to_prospect][:var] =
    leads_behavior[:leads_to_prospect][:previous].zero? ? 0 : (((leads_behavior[:leads_to_prospect][:current].to_f - leads_behavior[:leads_to_prospect][:previous].to_f) / leads_behavior[:leads_to_prospect][:previous].to_f) * 100).round(2)
  leads_behavior[:leads_to_customer] = { current: 0, previous: 0, var: 0 }
  leads_behavior[:leads_to_customer][:current] = leads_cur.map { |r| r[:to_customer] }.sum
  leads_behavior[:leads_to_customer][:previous] = leads_prev.map { |r| r[:to_customer] }.sum
  leads_behavior[:leads_to_customer][:var] =
    leads_behavior[:leads_to_customer][:previous].zero? ? 0 : (((leads_behavior[:leads_to_customer][:current].to_f - leads_behavior[:leads_to_customer][:previous].to_f) / leads_behavior[:leads_to_customer][:previous].to_f) * 100).round(2)

  leads_behavior
end

.metrics_trend_breakdown_by_week_or_month(values = [], type_breakdown) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 138

def self.metrics_trend_breakdown_by_week_or_month(values = [], type_breakdown)
  week = []
  month = []
  values.each do |ord|
    week << ord[:date].strftime('%V, %y')
    month << (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y'))
  end
  week = week.uniq
  month = month.uniq

  if type_breakdown == 2
    result = []
    date = ''
    num_orders = 0
    income = 0
    week.each do |w|
      values.each do |ord|
        date = 'W' + w.to_s
        num_orders += ord[:num_orders] if ord[:date].strftime('%V, %y') == w
        income += ord[:income] if ord[:date].strftime('%V, %y') == w
      end
      result << { date: date, num_orders: num_orders, income: income }
      date = ''
      num_orders = 0
      income = 0
    end
  else
    result = []
    date = ''
    num_orders = 0
    income = 0
    month.each do |m|
      values.each do |ord|
        date = m
        num_orders += ord[:num_orders] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m
        income += ord[:income] if (Date::ABBR_MONTHNAMES[ord[:date].strftime('%m').to_i] + ord[:date].strftime(', %y')) == m
      end
      result << { date: date, num_orders: num_orders, income: income }
      date = ''
      num_orders = 0
      income = 0
    end
  end

  result
end

.orders_by_origin(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1135

def self.orders_by_origin(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    select 1::int as id,'number_online_orders'::varchar as detail,count(order_id)::numeric as value
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code in (1,2)
    #{branch_sql}
    union all
    select 3::int as id,'number_iq_tool_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code = 3
    #{branch_sql}
    union all
    select 5::int as id,'number_crm_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code in (4,5,6)
    #{branch_sql};
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, detail: r[:detail], value: r[:value].to_f } }
  total_orders = 0
  results.map { |r| total_orders += r[:value] }
  results << (total_orders.zero? ? { id: 2, detail: 'percentage_online_orders', value: 0 } : { id: 2, detail: 'percentage_online_orders', value: ((results[0][:value] / total_orders) * 100) })
  results << (total_orders.zero? ? { id: 4, detail: 'percentage_iq_tool_orders', value: 0 } : { id: 4, detail: 'percentage_iq_tool_orders', value: ((results[1][:value] / total_orders) * 100) })
  results << (total_orders.zero? ? { id: 6, detail: 'percentage_crm_orders', value: 0 } : { id: 6, detail: 'percentage_crm_orders', value: ((results[2][:value] / total_orders) * 100) })
  results.sort_by { |k| k[:id] }
end

.orders_classification(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1224

def self.orders_classification(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    select 1::int as id,'online_current_orders'::varchar as detail,count(order_id)::numeric as value
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code = 1
    #{branch_sql}
    union all
    select 4::int as id,'online_iq_tool_current_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code = 3
    #{branch_sql}
    union all
    select 7::int as id,'online_crm_quote_current_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code = 2
    #{branch_sql}
    union all
    select 2::int as id,'online_previous_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and code = 1
    #{branch_sql}
    union all
    select 5::int as id,'online_iq_tool_previous_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and code = 3
    #{branch_sql}
    union all
    select 8::int as id,'online_crm_quote_previous_orders'::varchar,count(order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and code = 2
    #{branch_sql}
    order by 1;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, detail: r[:detail], value: r[:value].to_f } }
  results << (results[3][:value].zero? ? { id: 3, detail: 'online_percentage_orders', value: 0 } : { id: 3, detail: 'online_percentage_orders', value: (((results[0][:value] - results[1][:value]) / results[1][:value]) * 100) })
  results << if results[4][:value].zero?
               { id: 6, detail: 'online_iq_tool_percentage_orders',
             value: 0 }
             else
               { id: 6, detail: 'online_iq_tool_percentage_orders', value: (((results[2][:value] - results[3][:value]) / results[3][:value]) * 100) }
             end
  results << if results[5][:value].zero?
               { id: 9, detail: 'online_crm_quote_percentage_orders',
             value: 0 }
             else
               { id: 9, detail: 'online_crm_quote_percentage_orders', value: (((results[4][:value] - results[5][:value]) / results[5][:value]) * 100) }
             end
  results.sort_by { |k| k[:id] }
end

.orders_classification_graph(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1171

def self.orders_classification_graph(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    select id,detail,date,sum(value) as value
    from (
        select 2::int as id,'online_current_orders'::varchar as detail,date,0::numeric as value
        from analytic_date_time_dimensions
        where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        group by date
        union all
        select 2::int as id,'online_current_orders'::varchar as detail,ord_date,sum(revenue_consolidated)::numeric as value
        from view_orders_origin_facts
        where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        and code = 1
        #{branch_sql}
        group by ord_date
        union all
        select 4::int as id,'online_iq_tool_current_orders'::varchar as detail,date,0::numeric as value
        from analytic_date_time_dimensions
        where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        group by date
        union all
        select 4::int as id,'online_iq_tool_current_orders'::varchar as detail,ord_date,sum(revenue_consolidated)::numeric as value
        from view_orders_origin_facts
        where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        and code = 3
        #{branch_sql}
        group by ord_date
        union all
        select 5::int as id,'online_crm_quote_current_orders'::varchar as detail,date,0::numeric as value
        from analytic_date_time_dimensions
        where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        group by date
        union all
        select 5::int as id,'online_crm_quote_current_orders'::varchar as detail,ord_date,sum(revenue_consolidated)::numeric as value
        from view_orders_origin_facts
        where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        and code = 2
        #{branch_sql}
        group by ord_date
    )a
    group by id,detail,date
    order by id,date
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, detail: r[:detail], date: r[:date].to_date, value: r[:value].to_f } }
end

.orders_current_vs_previous(dates, type_data, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1287

def self.orders_current_vs_previous(dates, type_data, branch)
  var = type_data == 1 ? 'order_id ' : 'revenue_consolidated '
  func = type_data == 1 ? 'count(distinct order_id) ' : 'sum(revenue_consolidated) '

  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    WITH orders_cur as (
        select (row_number() over (ORDER BY a.date))::int as id,a.date as date_current,#{func}::numeric as current
        from (
            select case when weekday_name = 'Saturday' then (date + INTERVAL '2 day')::date
                        when weekday_name = 'Sunday' then (date + INTERVAL '1 day')::date else date end as date,#{var}
            from view_orders_origin_facts vof
            inner join analytic_date_time_dimensions dt on vof.ord_date = dt.date
            where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
            and code in (1,2,3)
            #{branch_sql}
        )a
        inner join analytic_date_time_dimensions dt2 on a.date = dt2.date
        group by a.date
        order by a.date

    ), orders_prev as (
        select (row_number() over (ORDER BY a.date))::int as id,a.date as date_previous,#{func}::numeric as previous
        from (
            select case when weekday_name = 'Saturday' then (date + INTERVAL '2 day')::date
                      when weekday_name = 'Sunday' then (date + INTERVAL '1 day')::date else date end as date,#{var}
            from view_orders_origin_facts vof
            inner join analytic_date_time_dimensions dt on vof.ord_date = dt.date
            where date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
            and code in (1,2,3)
            #{branch_sql}
        )a
        inner join analytic_date_time_dimensions dt2 on a.date = dt2.date
        group by a.date

    )
    select oc.id,date_current,date_previous,current,previous
    from orders_cur oc
    inner join orders_prev op on oc.id = op.id
    order by oc.id
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, date_current: r[:date_current].to_date, date_previous: r[:date_previous].to_date, current: r[:current].to_f, previous: r[:previous].to_f } }
end

.orders_metrics(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1053

def self.orders_metrics(dates, branch)
  days = (dates[:date_to] - dates[:date_from]) + 1
  days_prev = (dates[:date_to_prev] - dates[:date_from_prev]) + 1

  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    select 1::int as id,'current_orders'::varchar as detail,count(distinct order_id)::numeric as value
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code in (1,2,3)
    #{branch_sql}
    union all
    select 2::int as id,'previous_orders'::varchar,count(distinct order_id)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and code in (1,2,3)
    #{branch_sql}
    union all
    select 5::int as id,'current_revenue'::varchar,sum(revenue_consolidated)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code in (1,2,3)
    #{branch_sql}
    union all
    select 6::int as id,'previous_revenue'::varchar,sum(revenue_consolidated)::numeric
    from view_orders_origin_facts
    where ord_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and code in (1,2,3)
    #{branch_sql}
    order by 1;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, detail: r[:detail], value: r[:value].to_f } }
  results << (results[1][:value].zero? ? { id: 3, detail: 'compliance_percentage', value: 0 } : { id: 3, detail: 'compliance_percentage', value: ((results[0][:value] / results[1][:value]) * 100) })
  results << (results[1][:value].zero? ? { id: 4, detail: 'trend_percentage_num_orders', value: 0 } : { id: 4, detail: 'trend_percentage_num_orders', value: (((results[0][:value] - results[1][:value]) / results[1][:value]) * 100) })
  results << (results[3][:value].zero? ? { id: 7, detail: 'compliance_percentage', value: 0 } : { id: 7, detail: 'compliance_percentage', value: ((results[2][:value] / results[3][:value]) * 100) })
  results << (results[3][:value].zero? ? { id: 8, detail: 'trend_percentage_orders', value: 0 } : { id: 8, detail: 'trend_percentage_orders', value: (((results[2][:value] - results[3][:value]) / results[3][:value]) * 100) })
  results << (days.zero? ? { id: 9, detail: 'current_avg_orders_by_day', value: 0 } : { id: 9, detail: 'current_avg_orders_by_day', value: (results[0][:value] / days) })
  results << (days_prev.zero? ? { id: 10, detail: 'previous_avg_orders_by_day', value: 0 } : { id: 10, detail: 'previous_avg_orders_by_day', value: (results[1][:value] / days_prev) })
  results << (results[9][:value].zero? ? { id: 11, detail: 'trend_percentage_avg_orders', value: 0 } : { id: 11, detail: 'trend_percentage_avg_orders', value: (((results[8][:value] - results[9][:value]) / results[9][:value]) * 100) })
  results << (results[0][:value].zero? ? { id: 12, detail: 'current_avg_revenue_by_order', value: 0 } : { id: 12, detail: 'current_avg_revenue_by_order', value: (results[2][:value] / results[0][:value]) })
  results << (results[1][:value].zero? ? { id: 13, detail: 'previous_avg_revenue_by_order', value: 0 } : { id: 13, detail: 'previous_avg_revenue_by_order', value: (results[3][:value] / results[1][:value]) })
  results << if results[12][:value].zero?
               { id: 14, detail: 'trend_percentage_avg_revenue_by_order',
             value: 0 }
             else
               { id: 14, detail: 'trend_percentage_avg_revenue_by_order', value: (((results[11][:value] - results[12][:value]) / results[12][:value]) * 100) }
             end
  results.sort_by { |k| k[:id] }
end

.orders_metrics_trend(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 1109

def self.orders_metrics_trend(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql = <<-SQL
    select extract(dow from  a.date)::int as id,a.date,count(distinct order_id)::numeric as num_orders,sum(revenue_consolidated)::numeric as income
    from (
        select case when weekday_name = 'Saturday' then (date + INTERVAL '2 day')::date
                    when weekday_name = 'Sunday' then (date + INTERVAL '1 day')::date else date end as date,order_id,revenue_consolidated
        from view_orders_origin_facts vof
        inner join analytic_date_time_dimensions dt on vof.ord_date = dt.date
        where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        and code in (1,2,3)
        #{branch_sql}
    )a
    inner join analytic_date_time_dimensions dt2 on a.date = dt2.date
    group by a.date,weekday_name
    order by a.date;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, date: r[:date].to_date, num_orders: r[:num_orders].to_f, income: r[:income].to_f } }
end

.visits_by_region(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 514

def self.visits_by_region(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end
  sql = <<-SQL
    select (country_code || '-' || state_code)::varchar as region,sum(number_visits)::numeric as number_visits
    from view_visits_facts
    where (country_code is not null and state_code is not null)
    and visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    group by country_code,state_code
    order by 2 desc;
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { region: r[:region].to_s, number_visits: r[:number_visits].to_i } }
end

.visits_current_vs_previous(dates, code, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 908

def self.visits_current_vs_previous(dates, code, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  if code == 1
    sql_string = "WITH visits_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(number_visits)::numeric as current
                      from view_visits_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(number_visits)::numeric as previous
                      from view_visits_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      #{branch_sql}
                      group by visit_date
                  )
                  select vfc.id,date_current,date_previous,current,previous
                  from visits_facts_cur vfc
                  inner join visits_facts_prev vfp on vfc.id = vfp.id"
  elsif code == 2
    sql_string = "WITH visits_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(unique_visitors)::numeric as current
                      from view_visits_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(unique_visitors)::numeric as previous
                      from view_visits_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      #{branch_sql}
                      group by visit_date
                  )
                  select vfc.id,date_current,date_previous,current,previous
                  from visits_facts_cur vfc
                  inner join visits_facts_prev vfp on vfc.id = vfp.id"
  elsif code == 3
    sql_string = "WITH visits_detail_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(values)::numeric as current
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 1
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_detail_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(values)::numeric as previous
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      and code = 1
                      #{branch_sql}
                      group by visit_date
                  )
                  select vdc.id,date_current,date_previous,current,previous
                  from visits_detail_facts_cur vdc
                  inner join visits_detail_facts_prev vdp on vdc.id = vdp.id"
  elsif code == 31
    sql_string = "WITH visits_detail_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(values)::numeric as current
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 12
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_detail_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(values)::numeric as previous
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      and code = 12
                      #{branch_sql}
                      group by visit_date
                  )
                  select vdc.id,date_current,date_previous,current,previous
                  from visits_detail_facts_cur vdc
                  inner join visits_detail_facts_prev vdp on vdc.id = vdp.id"
  elsif code == 4
    sql_string = "WITH visits_detail_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(values)::numeric as current
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 2
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_detail_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(values)::numeric as previous
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      and code = 2
                      #{branch_sql}
                      group by visit_date
                  )
                  select vdc.id,date_current,date_previous,current,previous
                  from visits_detail_facts_cur vdc
                  inner join visits_detail_facts_prev vdp on vdc.id = vdp.id"
  elsif code == 5
    sql_string = "WITH visits_detail_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(values)::numeric as current
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 3
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_detail_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(values)::numeric as previous
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      and code = 3
                      #{branch_sql}
                      group by visit_date
                  )
                  select vdc.id,date_current,date_previous,current,previous
                  from visits_detail_facts_cur vdc
                  inner join visits_detail_facts_prev vdp on vdc.id = vdp.id"
  elsif code == 6
    sql_string = "WITH visits_detail_facts_cur as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_current,sum(values)::numeric as current
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 4
                      #{branch_sql}
                      group by visit_date order by visit_date
                  ), visits_detail_facts_prev as (
                      select (row_number() over (ORDER BY visit_date))::int as id,visit_date as date_previous,sum(values)::numeric as previous
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
                      and code = 4
                      #{branch_sql}
                      group by visit_date
                  )
                  select vdc.id,date_current,date_previous,current,previous
                  from visits_detail_facts_cur vdc
                  inner join visits_detail_facts_prev vdp on vdc.id = vdp.id"
  end

  sql = <<-SQL
    #{sql_string}
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, date_current: r[:date_current].to_date, date_previous: r[:date_previous].to_date, current: r[:current].to_f, previous: r[:previous].to_f } }
end

.visits_graphs(dates, code, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 798

def self.visits_graphs(dates, code, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end

  sql_string = if code == 1
                 "select extract(dow from visit_date)::int as id,visit_date as date,sum(website_us)::numeric as values
                  from view_visits_facts
                  where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  #{branch_sql}
                  group by visit_date order by visit_date"
               elsif code == 2
                 "select extract(dow from visit_date)::int as id,visit_date as date,sum(website_can)::numeric as values
                  from view_visits_facts
                  where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  #{branch_sql}
                  group by visit_date order by visit_date"
               elsif code == 3
                 "select id,date,sum(values) as values
                  from (
                      select extract(dow from visit_date)::int as id,visit_date as date,sum(values)::numeric as values
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code in (1,12)
                      #{branch_sql}
                      group by visit_date
                      union all
                      select extract(dow from date)::int as id,date,0::numeric as values
                      from analytic_date_time_dimensions
                      where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  )c
                  group by id,date order by date"
               elsif code == 4
                 "select id,date,sum(values) as values
                  from (
                      select extract(dow from visit_date)::int as id,visit_date as date,sum(values)::numeric as values
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 2
                      #{branch_sql}
                      group by visit_date
                      union all
                      select extract(dow from date)::int as id,date,0::numeric as values
                      from analytic_date_time_dimensions
                      where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  )c
                  group by id,date order by date"
               elsif code == 5
                 "select id,date,sum(values) as values
                  from (
                      select extract(dow from visit_date)::int as id,visit_date as date,sum(values)::numeric as values
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 3
                      #{branch_sql}
                      group by visit_date
                      union all
                      select extract(dow from date)::int as id,date,0::numeric as values
                      from analytic_date_time_dimensions
                      where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  )c
                  group by id,date order by date"
               elsif code == 6
                 "select id,date,sum(values) as values
                  from (
                      select extract(dow from visit_date)::int as id,visit_date as date,sum(values)::numeric as values
                      from view_visits_detail_facts
                      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                      and code = 4
                      #{branch_sql}
                      group by visit_date
                      union all
                      select extract(dow from date)::int as id,date,0::numeric as values
                      from analytic_date_time_dimensions
                      where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  )c
                  group by id,date order by date"
               elsif code == 7
                 "select extract(dow from visit_date)::int as id,visit_date as date,sum(number_visits)::numeric as values
                  from view_visits_facts
                  where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                  #{branch_sql}
                  group by visit_date order by visit_date"
               else
                 "select id,visit_date as date,sum(values) as values
                   from (
                       select extract(dow from visit_date)::int as id,visit_date,case when sum(values) is null then 0 else  sum(values)::numeric end as values
                       from view_visits_detail_facts
                       where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                       and code = #{code}
                       #{branch_sql}
                       group by visit_date
                       union all
                       select extract(dow from date)::int as id,date,0::numeric as values
                       from analytic_date_time_dimensions
                       where date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
                   )a
                   group by id,visit_date
                   order by visit_date"
               end

  sql = <<-SQL
    #{sql_string}
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, date: r[:date].to_date, values: r[:values].to_f } }
end

.visits_values(dates, branch) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 533

def self.visits_values(dates, branch)
  branch_sql = if branch.empty?
                 ' '
               else
                 branch[0] == 'USA' ? ' and company_id = 1 ' : ' and company_id = 2 '
               end
  sql = <<-SQL
    select 1::int as id,'current_visits'::varchar as detail,sum(number_visits)::numeric as value
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 2::int as id,'prev_visits'::varchar as detail,sum(number_visits)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    #{branch_sql}
    union all
    select 4::int as id,'current_visitors'::varchar,sum(unique_visitors)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 5::int as id,'prev_visitors'::varchar,sum(unique_visitors)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    #{branch_sql}
    union all
    select 7::int as id,'current_visits_us'::varchar as details,sum(website_us)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 8::int as id,'prev_visits_us'::varchar as details,sum(website_us)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    #{branch_sql}
    union all
    select 10::int as id,'current_visits_can'::varchar,sum(website_can)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 11::int as id,'prev_visits_can'::varchar,sum(website_can)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    #{branch_sql}
    union all
    select 13::int as id,'using_desktop'::varchar as details,sum(using_desktop)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 15::int as id,'using_mobile'::varchar as details,sum(using_mobile)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select 17::int as id,'using_tablet'::varchar,sum(using_tablet)::numeric
    from view_visits_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    #{branch_sql}
    union all
    select (code + 18)::int as id,(detail || ' percentage')::varchar as details, case when (select sum(values) from view_visits_detail_facts where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}' #{branch_sql} and code in (1,2,3,4,12)) = 0 then 0 else ((sum(values)::numeric / (select sum(values) from view_visits_detail_facts where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}' #{branch_sql} and code in (1,2,3,4,12))) * 100) end
    from (
      select case when code = 12 then 1 else code end code,case when code = 12 then 'Campaign traffic' else detail end as detail,values
      from view_visits_detail_facts
      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
      and code in (1,2,3,4,12)
      #{branch_sql}
    )a group by code,detail
    union all
    select (code + 22)::int as id,detail as details,sum(values)::numeric
    from (
      select case when code = 12 then 1 else code end code,case when code = 12 then 'Campaign traffic' else detail end as detail,values
      from view_visits_detail_facts
      where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
      and code in (1,2,3,4,12)
      #{branch_sql}
    )a
    group by code,detail
    union all
    select (code + 55)::int as id,(detail || ' prev') as details,sum(values)::numeric
    from (
      select case when code = 12 then 1 else code end code,case when code = 12 then 'Campaign traffic' else detail end as detail,values
      from view_visits_detail_facts
      where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
      and code in (1,2,3,4,12)
      #{branch_sql}
    )a
    group by code,detail
    union all
    select (code + 59)::int as id,(detail || ' variation')::varchar as details,(case when sum(prev_numbers) = 0 then 0 else ((sum(cur_numbers) / sum(prev_numbers)) - 1) * 100 end)::numeric
    from (
      select code,detail,sum(values)::numeric as cur_numbers,0::numeric as prev_numbers
      from (
          select case when code = 12 then 1 else code end code,case when code = 12 then 'Campaign traffic' else detail end as detail,values
          from view_visits_detail_facts
          where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
          and code in (1,2,3,4,12)
          #{branch_sql}
      )b group by code,detail
      union all
      select code,detail,0::numeric as cur_numbers,sum(values)::numeric as prev_numbers
      from (
          select case when code = 12 then 1 else code end code,case when code = 12 then 'Campaign traffic' else detail end as detail,values
          from view_visits_detail_facts
          where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
          and code in (1,2,3,4,12)
          #{branch_sql}
      )x group by code,detail
    )a
    group by code,detail
    union all
    select id,details,sum(values)::numeric
    from (
        select (code + 19)::int as id,detail as details,sum(values)::numeric as values
        from view_visits_detail_facts
        where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
        and code in (8,9,10,11)
        #{branch_sql}
        group by code,detail
        union all
        select distinct (code + 19)::int as id,detail as details,0::numeric
        from view_visits_detail_facts
        where code in (8,9,10,11)
        #{branch_sql}
    )a
    group by id,details
    union all
    select 31::int as id,'current transaction income'::varchar as details,coalesce(sum(revenue),0)::numeric
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 33::int as id,'previous transaction income'::varchar as details,sum(revenue)::numeric
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select (code + 29)::int as id,detail as details,sum(values)::numeric
    from view_visits_detail_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and code in (5,6,7)
    #{branch_sql}
    group by code,detail
    union all
    select 37::int as id,'conversion number from bing'::varchar,count(*)::numeric
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'bing'
    #{branch_sql}
    union all
    select 38::int as id,'conversion number from yahoo'::varchar,count(*)::numeric
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'yahoo'
    #{branch_sql}
    union all
    select 39::int as id,'conversion number from google'::varchar,count(*)::numeric
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'google'
    #{branch_sql}
    union all
    select 43::int as id,'conversion income from bing'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'bing'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 44::int as id,'conversion income from yahoo'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'yahoo'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 45::int as id,'conversion income from google'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from]}' and '#{dates[:date_to]}'
    and conversion_from = 'google'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 46::int as id,'conversion income from bing prev'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and conversion_from = 'bing'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 47::int as id,'conversion income from yahoo prev'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and conversion_from = 'yahoo'
    and state = 'invoiced'
    #{branch_sql}
    union all
    select 48::int as id,'conversion income from google prev'::varchar,case when sum(revenue) is null then 0 else sum(revenue)::numeric end
    from view_visits_conversion_facts
    where visit_date between '#{dates[:date_from_prev]}' and '#{dates[:date_to_prev]}'
    and conversion_from = 'google'
    and state = 'invoiced'
    #{branch_sql}
    order by 1
  SQL

  results = ActiveRecord::Base.connection.execute(sql).to_a.map { |r| r.symbolize_keys }.map { |r| { id: r[:id].to_i, detail: r[:detail].to_s, value: r[:value].to_f } }

  # Guarantee all expected SQL-sourced IDs are present so that the value_at
  # positional index lookups below always land on the correct row, even when
  # some traffic-source codes produce no rows (e.g. no referral/organic data
  # in the selected date range).
  expected_sql_ids = [1, 2, 4, 5, 7, 8, 10, 11, 13, 15, 17,
                      19, 20, 21, 22, 23, 24, 25, 26,
                      27, 28, 29, 30, 31, 33,
                      34, 35, 36, 37, 38, 39,
                      43, 44, 45, 46, 47, 48,
                      56, 57, 58, 59, 60, 61, 62, 63]
  results_by_id = results.index_by { |r| r[:id] }
  results = expected_sql_ids.map { |id| results_by_id[id] || { id: id, detail: 'n/a', value: 0.0 } }

  value_at = ->(idx) { results[idx] && results[idx][:value] ? results[idx][:value].to_f : 0.0 }
  total_devices = 0
  results << (value_at.call(1).zero? ? { id: 3, detail: 'trend_percentage_visits', value: 0 } : { id: 3, detail: 'trend_percentage_visits', value: (((value_at.call(0) - value_at.call(1)) / value_at.call(1)) * 100) })
  results << (value_at.call(3).zero? ? { id: 6, detail: 'trend_percentage_unique_visitors', value: 0 } : { id: 6, detail: 'trend_percentage_unique_visitors', value: (((value_at.call(2) - value_at.call(3)) / value_at.call(3)) * 100) })
  results << (value_at.call(5).zero? ? { id: 9, detail: 'trend_percentage_visits_us', value: 0 } : { id: 9, detail: 'trend_percentage_visits_us', value: (((value_at.call(4) - value_at.call(5)) / value_at.call(5)) * 100) })
  results << (value_at.call(7).zero? ? { id: 12, detail: 'trend_percentage_visits_can', value: 0 } : { id: 12, detail: 'trend_percentage_visits_can', value: (((value_at.call(6) - value_at.call(7)) / value_at.call(7)) * 100) })
  total_devices = value_at.call(8) + value_at.call(9) + value_at.call(10)
  results << (total_devices.zero? ? { id: 14, detail: 'desktop_percentage', value: 0 } : { id: 14, detail: 'desktop_percentage', value: ((value_at.call(8) / total_devices) * 100) })
  results << (total_devices.zero? ? { id: 16, detail: 'mobile_percentage', value: 0 } : { id: 16, detail: 'mobile_percentage', value: ((value_at.call(9) / total_devices) * 100) })
  results << (total_devices.zero? ? { id: 18, detail: 'tablet_percentage', value: 0 } : { id: 18, detail: 'tablet_percentage', value: ((value_at.call(10) / total_devices) * 100) })
  results << (value_at.call(24).zero? ? { id: 32, detail: 'trend_percentage_conversion', value: 0 } : { id: 32, detail: 'trend_percentage_conversion', value: (((value_at.call(23) - value_at.call(24)) / value_at.call(24)) * 100) })
  results << (value_at.call(25).zero? ? { id: 40, detail: 'trend_percentage_conversion_bing', value: 0 } : { id: 40, detail: 'trend_percentage_conversion_bing', value: ((value_at.call(28) / value_at.call(25)) * 100) })
  results << (value_at.call(26).zero? ? { id: 41, detail: 'trend_percentage_conversion_yahoo', value: 0 } : { id: 41, detail: 'trend_percentage_conversion_yahoo', value: ((value_at.call(29) / value_at.call(26)) * 100) })
  results << (value_at.call(27).zero? ? { id: 42, detail: 'trend_percentage_conversion_google', value: 0 } : { id: 42, detail: 'trend_percentage_conversion_google', value: ((value_at.call(30) / value_at.call(27)) * 100) })
  results << if value_at.call(34).zero?
               { id: 49, detail: 'trend_percentage_conversion_bing',
             value: 0 }
             else
               { id: 49, detail: 'trend_percentage_conversion_bing', value: (((value_at.call(31) - value_at.call(34)) / value_at.call(34)) * 100) }
             end
  results << if value_at.call(35).zero?
               { id: 50, detail: 'trend_percentage_conversion_yahoo',
             value: 0 }
             else
               { id: 50, detail: 'trend_percentage_conversion_yahoo', value: (((value_at.call(32) - value_at.call(35)) / value_at.call(35)) * 100) }
             end
  results << if value_at.call(36).zero?
               { id: 51, detail: 'trend_percentage_conversion_google',
             value: 0 }
             else
               { id: 51, detail: 'trend_percentage_conversion_google', value: (((value_at.call(33) - value_at.call(36)) / value_at.call(36)) * 100) }
             end
  results << (value_at.call(0).zero? ? { id: 52, detail: 'visits_products_view_percentage', value: 0 } : { id: 52, detail: 'visits_products_view_percentage', value: ((value_at.call(19) / value_at.call(0)) * 100) })
  results << (value_at.call(0).zero? ? { id: 53, detail: 'visits_add_to_cart_percentage', value: 0 } : { id: 53, detail: 'visits_add_to_cart_percentage', value: ((value_at.call(20) / value_at.call(0)) * 100) })
  results << (value_at.call(0).zero? ? { id: 54, detail: 'visits_check_out_percentage', value: 0 } : { id: 54, detail: 'visits_check_out_percentage', value: ((value_at.call(21) / value_at.call(0)) * 100) })
  results << (value_at.call(0).zero? ? { id: 55, detail: 'visits_transactions_percentage', value: 0 } : { id: 55, detail: 'visits_transactions_percentage', value: ((value_at.call(22) / value_at.call(0)) * 100) })

  results.sort_by { |k| k[:id] }
end

.web_site_performance_result(options = {}) ⇒ Object

# File 'app/services/report/web_site_performance/web_site_performance.rb', line 7

def self.web_site_performance_result(options = {})
  start_date = options[:period1_gteq]
  end_date = options[:period1_lteq]
  compare_start_date = options[:period2_gteq]
  compare_end_date = options[:period2_lteq]
  branch = if options[:branch].empty?
             nil
           else
             (options[:branch][0] == 'USA' ? 1 : 2)
           end
  dates = dates_to_check(start_date, end_date, compare_start_date, compare_end_date)

  #----------- Queries for ORDERS section
  orders_metrics = orders_metrics(dates, options[:branch])
  orders_metrics_trend = orders_metrics_trend(dates, options[:branch])
  orders_num_cur_prev = orders_current_vs_previous(dates, 1, options[:branch])
  orders_income_cur_prev = orders_current_vs_previous(dates, 2, options[:branch])
  orders_class = orders_classification(dates, options[:branch])
  orders_class_graph = orders_classification_graph(dates, options[:branch])
  orders_origin = orders_by_origin(dates, options[:branch])
  orders_metrics_trend = metrics_trend_breakdown_by_week_or_month(orders_metrics_trend, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  orders_num_cur_prev = current_vs_previous_breakdown_by_week_or_month(orders_num_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  orders_income_cur_prev = current_vs_previous_breakdown_by_week_or_month(orders_income_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  orders_class_graph = classification_breakdown_by_week_or_month(orders_class_graph, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1

  #----------- Queries for VISITS section
  visits_by_region = visits_by_region(dates, options[:branch])
  visits_values = visits_values(dates, options[:branch])
  website_us = visits_graphs(dates, 1, options[:branch])
  website_can = visits_graphs(dates, 2, options[:branch])
  campaign = visits_graphs(dates, 3, options[:branch])
  direct = visits_graphs(dates, 4, options[:branch])
  referral = visits_graphs(dates, 5, options[:branch])
  organic = visits_graphs(dates, 6, options[:branch])
  total_visits = visits_graphs(dates, 7, options[:branch])
  product_views = visits_graphs(dates, 8, options[:branch])
  add_to_cart = visits_graphs(dates, 9, options[:branch])
  check_out = visits_graphs(dates, 10, options[:branch])
  transactions = visits_graphs(dates, 11, options[:branch])
  visits_cur_prev = visits_current_vs_previous(dates, 1, options[:branch])
  visitors_cur_prev = visits_current_vs_previous(dates, 2, options[:branch])
  campaign_cur_prev = visits_current_vs_previous(dates, 3, options[:branch])
  campaignppc_cur_prev = visits_current_vs_previous(dates, 31, options[:branch])
  direct_cur_prev = visits_current_vs_previous(dates, 4, options[:branch])
  referral_cur_prev = visits_current_vs_previous(dates, 5, options[:branch])
  organic_cur_prev = visits_current_vs_previous(dates, 6, options[:branch])

  visits_cur_prev = current_vs_previous_breakdown_by_week_or_month(visits_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  visitors_cur_prev = current_vs_previous_breakdown_by_week_or_month(visitors_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  website_us = breakdown_by_week_or_month(website_us, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  website_can = breakdown_by_week_or_month(website_can, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  campaign = breakdown_by_week_or_month(campaign, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  campaign_cur_prev = current_vs_previous_breakdown_by_week_or_month(campaign_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  campaignppc_cur_prev = current_vs_previous_breakdown_by_week_or_month(campaignppc_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  direct = breakdown_by_week_or_month(direct, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  direct_cur_prev = current_vs_previous_breakdown_by_week_or_month(direct_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  referral = breakdown_by_week_or_month(referral, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  referral_cur_prev = current_vs_previous_breakdown_by_week_or_month(referral_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  organic = breakdown_by_week_or_month(organic, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  organic_cur_prev = current_vs_previous_breakdown_by_week_or_month(organic_cur_prev, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  total_visits = breakdown_by_week_or_month(total_visits, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  product_views = breakdown_by_week_or_month(product_views, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  add_to_cart = breakdown_by_week_or_month(add_to_cart, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  check_out = breakdown_by_week_or_month(check_out, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1
  transactions = breakdown_by_week_or_month(transactions, options[:breakdown][0].to_i) if options[:breakdown][0].to_i != 1

  if Rails.env.production?
    #----------- LEADs section
    current_period = dates[:date_from]..dates[:date_to]
    prev_period = dates[:date_from_prev]..dates[:date_to_prev]
    lead_sd = 31.days.ago.to_date
    lead_ed = 1.day.ago.to_date
    lead_sd_prev = lead_sd.years_ago(1).to_date
    lead_ed_prev = lead_ed.years_ago(1).to_date
    leads_cur = get_leads(lead_sd, lead_ed)
    leads_prev = get_leads(lead_sd_prev, lead_ed_prev)
    leads_behavior = get_values_lifecycle_over_time(leads_cur, leads_prev)
    leads_behavior_chart = get_data_for_charts(leads_cur)
    current_conversion = get_data_convertions(dates[:date_from], dates[:date_to])
    prev_conversion = get_data_convertions(dates[:date_from_prev], dates[:date_to_prev])
  end

  Result.new(success: true,
             orders_metrics: orders_metrics,
             orders_metrics_trend: orders_metrics_trend,
             orders_num_cur_prev: orders_num_cur_prev,
             orders_income_cur_prev: orders_income_cur_prev,
             orders_class: orders_class,
             orders_class_graph: orders_class_graph,
             orders_by_origin: orders_origin,
             visits_values: visits_values,
             total_visits: total_visits,
             product_views: product_views,
             add_to_cart: add_to_cart,
             check_out: check_out,
             transactions: transactions,
             visits_cur_prev: visits_cur_prev,
             visitors_cur_prev: visitors_cur_prev,
             website_us: website_us,
             website_can: website_can,
             visits_by_region: visits_by_region,
             campaign: campaign,
             campaign_cur_prev: campaign_cur_prev,
             campaignppc_cur_prev: campaignppc_cur_prev,
             direct: direct,
             direct_cur_prev: direct_cur_prev,
             referral: referral,
             referral_cur_prev: referral_cur_prev,
             organic: organic,
             organic_cur_prev: organic_cur_prev,
             leads_behavior: leads_behavior,
             leads_behavior_chart: leads_behavior_chart,
             current_conversion: current_conversion,
             prev_conversion: prev_conversion,
             type_breakdown: options[:breakdown][0].to_i,
             branch: branch,
             date_from: dates[:date_from],
             date_to: dates[:date_to],
             date_from_prev: dates[:date_from_prev],
             date_to_prev: dates[:date_to_prev])
end