jam-cloud/ruby/lib/jam_ruby/models/email_batch_progression.rb

# -*- coding: utf-8 -*-
module JamRuby
  class EmailBatchProgression < EmailBatchPeriodic

    BATCH_SIZE = 500
    SINCE_WEEKS = 2
    NUM_IDX = 3

    SUBTYPES = [:client_notdl, # Registered Musician Has Not Downloaded Client
                :client_dl_notrun, # Registered Musician Has Downloaded Client But Not Yet Run It
                :client_run_notgear, # Registered Musician Has Run Client But Not Successfully Qualified Audio Gear
                :gear_notsess, # Registered Musician Has Successfully Qualified Audio Gear But Has Not Participated in a ‘Real’ Session
                :sess_notgood, # Registered Musician Has Participated In a "Real" Session But Has Not Had a "Good" Session
                :sess_notrecord, # Registered Musician Has Participated In a "Real" Session But Has Not Made a Recording
                :reg_notinvite, # Registered Musician Has Not Invited Friends to Join JamKazam
                :reg_notconnect, # Registered Musician Has Not Connected with any Friends on JamKazam
                :reg_notlike, # Registered Musician Has Not Liked Jamkazam
               ]

    SUBTYPE_METADATA = {
      :client_notdl => {
        :subject => "Get the free JamKazam app now and start playing with others!", 
        :title => "Download the Free JamKazam App"
      },
      :client_dl_notrun => {
        :subject => "Having trouble running the JamKazam application?", 
        :title => "Running the JamKazam App"
      },
      :client_run_notgear => {
        :subject => "Having trouble setting up your audio gear for JamKazam?", 
        :title => "Setting Up and Qualifying Your Audio Gear on JamKazam"
      },
      :gear_notsess => {
        :subject => "Having trouble getting into a session with other musicians?", 
        :title => "Tips on Getting into Sessions with Other Musicians"
      },
      :sess_notgood => {
        :subject => "Have you played in a “good” session on JamKazam yet?", 
        :title => "Having a Good Session on JamKazam"
      },
      :reg_notinvite => {
        :subject => "Invite your friends to JamKazam, best way to play online!", 
        :title => "Invite Your Friends to Come and Play with You"
      },
      :reg_notconnect => {
        :subject => "Make friends on JamKazam and play more music!", 
        :title => "Connecting with Friends on JamKazam"
      },
      :reg_notlike => {
        :subject => "Please give us a LIKE!", 
        :title => "Like/Follow JamKazam"
      },
      :sess_notrecord => {
        :subject => "Want to make recordings during your JamKazam sessions?", 
        :title => "Making & Sharing Recordings on JamKazam"
      },
    }

    def self.subject(subtype=nil)
      SUBTYPE_METADATA[subtype][:subject] if subtype
    end

    def self.title(subtype=nil)
      SUBTYPE_METADATA[subtype][:title] if subtype
    end

    def self.subtype_trigger_interval(subtype)
      [:reg_notlike, :sess_notrecord].include?(subtype) ? [7,14,21] : [2,5,14]
    end

    def self.days_past_for_trigger_index(subtype, idx)
      self.subtype_trigger_interval(subtype)[idx]
    end

    def self.min_create_date
      min = self.unscoped 
        .where(:aasm_state => 'delivered')
        .order('created_at ASC')
        .limit(1)
        .pluck(:created_at)
        .first || Time.now
      min.to_date.strftime('%Y-%m-%d')
    end

    def days_diff(tidx)
      self.class.days_past_for_trigger_index(self.sub_type,tidx) - self.class.days_past_for_trigger_index(self.sub_type, tidx - 1)
    end

    def days_past_for_trigger_index(idx)
      self.class.subtype_trigger_interval(self.sub_type)[idx]
    end

    def make_set(uu, trigger_idx)
      EmailBatchSet.progress_set(self, uu, trigger_idx)
    end
    
    def trigger_date_constraint(trigger_idx, tbl='tt')
      intervals = self.class.subtype_trigger_interval(self.sub_type)
      date_constraint = (Time.now - intervals[trigger_idx].days + 1.hour).strftime('%Y-%m-%d %H:%M:%S')

      case self.sub_type.to_sym
      when :client_notdl, :reg_notconnect, :reg_notinvite, :reg_notlike
        return "#{tbl}.created_at < '#{date_constraint}'" 
      when :client_dl_notrun
        return "#{tbl}.first_downloaded_client_at < '#{date_constraint}'" 
      when :client_run_notgear
        return "#{tbl}.first_ran_client_at < '#{date_constraint}'" 
      when :gear_notsess
        return "#{tbl}.first_certified_gear_at < '#{date_constraint}'" 
      when :sess_notgood
        return "#{tbl}.first_real_music_session_at < '#{date_constraint}'" 
      when :sess_notrecord
        return "#{tbl}.first_real_music_session_at < '#{date_constraint}'" 
      end
    end

    def progress_column_constraint(tbl='tt')
      case self.sub_type.to_sym
      when :client_notdl
        return "#{tbl}.first_downloaded_client_at IS NULL"
      when :client_dl_notrun
        return "#{tbl}.first_downloaded_client_at IS NOT NULL AND #{tbl}.first_ran_client_at IS NULL"
      when :client_run_notgear
        return "#{tbl}.first_ran_client_at IS NOT NULL AND #{tbl}.first_certified_gear_at IS NULL"
      when :gear_notsess
        return "#{tbl}.first_certified_gear_at IS NOT NULL AND #{tbl}.first_real_music_session_at IS NULL"
      when :sess_notgood
        return "#{tbl}.first_real_music_session_at IS NOT NULL AND #{tbl}.first_good_music_session_at IS NULL"
      when :sess_notrecord
        return "#{tbl}.first_real_music_session_at IS NOT NULL AND #{tbl}.first_recording_at IS NULL"
      when :reg_notinvite
        return "#{tbl}.first_invited_at IS NULL"
      when :reg_notconnect
        return "#{tbl}.first_friended_at IS NULL"
      when :reg_notlike
        return "#{tbl}.first_social_promoted_at IS NULL"
      end
      ''
    end

    def fetch_recipients(trigger_idx=0, per_page=BATCH_SIZE)
      fetched = []
      offset = 0
      if 0==trigger_idx
        prev_date_sql = 'tt.prev_trigger_date IS NULL' 
      else
        prev_date_sql = "tt.prev_trigger_date + interval '#{self.days_diff(trigger_idx)} days' <= '#{Time.now.strftime('%Y-%m-%d %H:%M:%S')}'"
      end
      countsql =<<SQL
SELECT COUNT(*)
FROM (SELECT "users".*, 
       (SELECT COALESCE(MAX(ebs.trigger_index),-1) 
          FROM email_batch_sets ebs 
          WHERE 
            ebs.sub_type = '#{self.sub_type}' AND 
            ebs.user_id = users.id
       ) AS tidx,
       (SELECT created_at 
         FROM email_batch_sets ebs 
         WHERE 
           ebs.sub_type = '#{self.sub_type}' AND 
           ebs.user_id = users.id AND trigger_index = #{trigger_idx - 1}
        ) AS prev_trigger_date
      FROM users) tt
WHERE 
  tt."subscribe_email" = 't' AND 
  tt."musician" = 't' AND 
  (#{progress_column_constraint}) AND 
  (#{self.trigger_date_constraint(trigger_idx)}) AND 
  tt.tidx = #{trigger_idx - 1} AND 
  #{prev_date_sql}
SQL
      num_users = User.count_by_sql(countsql)
      loops = (num_users / per_page) + (num_users % per_page) - 1
      0.upto(loops) do |nn|
        offset = nn * per_page
        sql =<<SQL
SELECT tt.*
FROM (SELECT "users".*, 
       (SELECT COALESCE(MAX(ebs.trigger_index),-1) 
          FROM email_batch_sets ebs 
          WHERE 
            ebs.sub_type = '#{self.sub_type}' AND 
            ebs.user_id = users.id
       ) AS tidx,
       (SELECT created_at 
         FROM email_batch_sets ebs 
         WHERE 
           ebs.sub_type = '#{self.sub_type}' AND 
           ebs.user_id = users.id AND trigger_index = #{trigger_idx - 1}
        ) AS prev_trigger_date
      FROM users) tt
WHERE 
  tt."subscribe_email" = 't' AND 
  tt."musician" = 't' AND 
  (#{progress_column_constraint}) AND 
  (#{self.trigger_date_constraint(trigger_idx)}) AND 
  tt.tidx = #{trigger_idx - 1} AND 
  #{prev_date_sql}
ORDER BY tt.id 
ASC LIMIT #{per_page}
OFFSET #{offset}
SQL
        users = User.find_by_sql(sql)
        block_given? ? yield(users) : fetched.concat(users)
      end
      fetched
    end

    def deliver_batch_sets!
      self.opt_in_count = 0
      sent = 0
      3.times do |trigger_idx| 
        self.fetch_recipients(trigger_idx) do |users|
          users.each do |uu|
            self.email_batch_sets << (bset = self.make_set(uu, trigger_idx))
            ProgressMailer.send_reminder(bset).deliver_now
            sent += 1
          end
        end
      end
      self.sent_count = sent
      self.save
      self.did_batch_run!
    end

    def self.send_progress_batch
      self::SUBTYPES.collect do |stype|
        ebatch = self.create
        ebatch.update_attribute(:sub_type, stype.to_s)
        ebatch
      end.each do |ebatch|
        ebatch.deliver_batch
      end
    end

  end
end