csv_generator.rb 8.48 KB
Newer Older
Maxwell Salzberg's avatar
Maxwell Salzberg committed
1 2
module CsvGenerator

3
  PATH = '/tmp/'
4 5
  BACKER_CSV_LOCATION = File.join('/usr/local/app/diaspora/', 'backer_list.csv')
  #BACKER_CSV_LOCATION = File.join('/home/ilya/workspace/diaspora/', 'backer_list.csv')
6 7
  WAITLIST_LOCATION = File.join(Rails.root, 'config', 'mailing_list.csv')
  OFFSET_LOCATION = File.join(Rails.root, 'config', 'email_offset')
8
  UNSUBSCRIBE_LOCATION = File.join(Rails.root, 'config', 'unsubscribe.csv')
Maxwell Salzberg's avatar
Maxwell Salzberg committed
9 10 11 12 13 14 15 16

  def self.all_active_users
    file = self.filename("all_active_users")
    sql = <<SQL
      SELECT email AS '%EMAIL%' 
        #{self.output_syntax(file)}
        FROM users where username IS NOT NULL
SQL
17
    ActiveRecord::Base.connection.execute(sql)
Maxwell Salzberg's avatar
Maxwell Salzberg committed
18 19 20 21 22 23 24 25 26 27 28 29 30
  end

  def self.all_inactive_invited_users
    file = self.filename("all_inactive_invited_users.csv")
    sql = <<SQL
      SELECT invitations.identifier AS '%EMAIL%', users.invitation_token AS '%TOKEN%'
        #{self.output_syntax(file)}
        FROM invitations
        JOIN users ON
          users.id=invitations.recipient_id
          WHERE users.username IS NULL
            AND invitations.service='email'
SQL
31 32 33 34
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.generate_csvs
35
    #`mkdir /tmp/csvs`
36 37 38 39 40 41
    self.backers_recent_login
    self.backers_old_login
    self.backers_never_login
    self.non_backers_recent_login
    self.non_backers_old_login
    self.non_backers_never_login
Maxwell Salzberg's avatar
Maxwell Salzberg committed
42 43
  end

44 45 46 47 48 49 50 51
  def self.all_users
    file = self.filename("v1_9_20_all_users.csv")
    sql = self.select_fragment(file, "#{self.has_email}" +
                                " AND #{self.unsubscribe_email_condition}")

    ActiveRecord::Base.connection.execute(sql)
  end

52 53
  def self.backers_recent_login
    file = self.filename("v1_backers_recent_login.csv")
54 55
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition}" +
                                " AND #{self.unsubscribe_email_condition} AND #{self.recent_login_query}")
Maxwell Salzberg's avatar
Maxwell Salzberg committed
56

57 58 59 60 61
    ActiveRecord::Base.connection.execute(sql)
  end

  def self.backers_old_login
    file = self.filename("v2_backers_old_login.csv")
62 63
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} " +
                                " AND #{self.unsubscribe_email_condition} AND #{self.old_login_query}")
Ilyaaaaaaaaaaaaa Zhitomirskiy's avatar
Ilyaaaaaaaaaaaaa Zhitomirskiy committed
64

65
    ActiveRecord::Base.connection.execute(sql)
66
  end
Maxwell Salzberg's avatar
Maxwell Salzberg committed
67

68
  def self.backers_never_login
69
    #IF(`users`.invitation_token,   ,NULL)
70
    file = self.filename("v3_backers_never_login.csv")
71 72 73 74
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
75
                    'Friend of Diaspora*' AS '%NAME%',
76
                CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) AS '%INVITATION_LINK%'
77 78
                #{self.output_syntax(file)}
             FROM `users`
79
            WHERE #{self.has_email} AND #{self.has_invitation_token} AND #{self.backer_email_condition} AND #{self.unsubscribe_email_condition} AND #{self.never_login_query};
80
SQL
81 82 83 84 85 86

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_recent_login
    file = self.filename("v4_non_backers_recent_login.csv")
87 88
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} " +
                                "AND #{self.unsubscribe_email_condition} AND #{self.recent_login_query}")
89 90 91 92 93 94

    ActiveRecord::Base.connection.execute(sql)
  end

  def self.non_backers_old_login
    file = self.filename("v5_non_backers_old_login.csv")
95 96
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} " +
                                "AND #{self.unsubscribe_email_condition} AND #{self.old_login_query}")
97 98 99

    ActiveRecord::Base.connection.execute(sql)
  end
Ilyaaaaaaaaaaaaa Zhitomirskiy's avatar
Ilyaaaaaaaaaaaaa Zhitomirskiy committed
100

101 102
  def self.non_backers_never_login
    file = self.filename("v6_non_backers_never_login.csv")
103 104 105 106
    sql = <<SQL
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
          UNION
            SELECT `users`.email AS '%EMAIL%',
107
                    'Friend of Diaspora*' AS '%NAME%',
108
                CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) AS '%INVITATION_LINK%'
109 110
                #{self.output_syntax(file)}
             FROM `users`
111
            WHERE #{self.has_email} AND #{self.has_invitation_token} AND #{self.non_backer_email_condition} AND #{self.unsubscribe_email_condition} AND #{self.never_login_query};
112
SQL
113 114
    ActiveRecord::Base.connection.execute(sql)
  end
115

116 117 118 119 120 121 122 123
  def self.non_backers_logged_in
    file = self.filename("v2_non_backers.csv")
    sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} " +
                                "AND #{self.unsubscribe_email_condition} AND #{self.has_username}")

    ActiveRecord::Base.connection.execute(sql)
  end

124
  # ---------------- QUERY METHODS & NOTES -------------------------
125 126
  def self.select_fragment(file, where_clause)
    sql = <<SQL
127
          SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'
128 129
          UNION
            SELECT `users`.email AS '%EMAIL%',
130 131
                   IF( `profiles`.first_name IS NOT NULL AND `profiles`.first_name != "",
                                               `profiles`.first_name, 'Friend of Diaspora*') AS '%NAME%',
132
                IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'
133 134 135 136 137 138 139
                #{self.output_syntax(file)}
             FROM `users`
             JOIN `people` ON `users`.id = `people`.owner_id JOIN `profiles` ON `people`.id = `profiles`.person_id
            WHERE #{where_clause};
SQL
  end

140 141 142 143
  def self.has_username
    '`users`.`username` IS NOT NULL'
  end
  
144
  def self.has_invitation_token
145
    '`users`.`invitation_token` IS NOT NULL'
146 147
  end
  
148 149 150 151
  def self.has_email
    '`users`.`email` IS NOT NULL AND `users`.`email` != ""'
  end

152
  def self.backer_email_condition
Ilyaaaaaaaaaaaaa Zhitomirskiy's avatar
Ilyaaaaaaaaaaaaa Zhitomirskiy committed
153 154 155
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
156 157
  end

158 159 160 161 162 163
  def self.non_backer_email_condition
    b_emails = self.backer_emails
    b_emails.map!{|a| "'#{a}'"}
    "`users`.`email` NOT IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
  end

164 165 166 167 168 169
  def self.unsubscribe_email_condition
    u_emails = self.unsubscriber_emails
    u_emails.map!{|a| "'#{a}'"}
    "`users`.`email` NOT IN (#{query_string_from_array(u_emails[1..u_emails.length])})" 
  end

170
  def self.recent_login_query
171
    "(last_sign_in_at > SUBDATE(NOW(), INTERVAL 31 DAY))"
172 173 174
  end

  def self.old_login_query
175
    "(last_sign_in_at < SUBDATE(NOW(), INTERVAL 31 DAY))"
176
  end
177

178 179
  def self.never_login_query
    "(last_sign_in_at IS NULL)"
180 181 182 183
  end
  
  def self.query_string_from_array(array)
    array.join(", ")
Maxwell Salzberg's avatar
Maxwell Salzberg committed
184
  end
185 186 187 188 189
  
  # BACKER RECENT LOGIN
  # User.where("last_sign_in_at > ?", (Time.now - 1.month).to_i).where(:email => ["maxwell@joindiaspora.com"]).count
  #
  # "SELECT `users`.* FROM `users` WHERE `users`.`email` IN ('maxwell@joindiaspora.com') AND (last_sign_in_at > 1312663724)"
Maxwell Salzberg's avatar
Maxwell Salzberg committed
190

191 192 193 194 195 196 197 198 199 200
  # NON BACKER RECENT LOGIN
  # User.where("last_sign_in_at > ?", (Time.now - 1.month).to_i).where("email NOT IN (?)", 'maxwell@joindiaspora.com').to_sql
  # "SELECT `users`.* FROM `users` WHERE (last_sign_in_at > 1312665370) AND (email NOT IN ('maxwell@joindiaspora.com'))" 
 



  # ---------------- HELPER METHODS -------------------------
  def self.load_waiting_list_csv(filename)
    csv = filename
Maxwell Salzberg's avatar
Maxwell Salzberg committed
201 202 203 204 205 206 207 208 209 210 211
    if RUBY_VERSION.include? "1.8"
      require 'fastercsv'
       people = FasterCSV.read(csv)
     else
       require 'csv'
       people = CSV.read(csv)
     end
    people
  end

  def self.offset
212
    offset_filename = OFFSET_LOCATION
Maxwell Salzberg's avatar
Maxwell Salzberg committed
213 214 215 216 217 218 219 220 221 222 223 224 225 226
    File.read(offset_filename).to_i
  end

  def self.filename(name)
    "#{PATH}#{Time.now.strftime("%Y-%m-%d")}-#{name}"
  end

  def self.output_syntax filename
    <<SQL
    INTO OUTFILE '#{filename}'
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
SQL
  end
227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244

  def self.waitlist
    people = self.load_waiting_list_csv(WAITLIST_LOCATION)
    offset = self.offset
    left = people[0...offset]
    right = people[offset...people.size]

    #reading from csv (get number of row we're on) - left
    #reading from csv (get number of row we're on) - right
  end

  def self.backers
    self.load_waiting_list_csv(BACKER_CSV_LOCATION)
  end

  def self.backer_emails
    self.backers.map{|b| b[0]}
  end
245 246 247 248 249 250 251 252

  def self.unsubsribers
    self.load_waiting_list_csv(UNSUBSCRIBE_LOCATION)
  end

  def self.unsubscriber_emails
    self.unsubsribers.map{|b| b[1]}
  end
Maxwell Salzberg's avatar
Maxwell Salzberg committed
253
end