Module: CsvGenerator

Defined in:
lib/csv_generator.rb

Constant Summary collapse

PATH =
'/tmp/'
BACKER_CSV_LOCATION =
File.join('/usr/local/app/diaspora/', 'backer_list.csv')
WAITLIST_LOCATION =

BACKER_CSV_LOCATION = File.join('/home/ilya/workspace/diaspora/', 'backer_list.csv')

Rails.root.join('config', 'mailing_list.csv')
OFFSET_LOCATION =
Rails.root.join('config', 'email_offset')
UNSUBSCRIBE_LOCATION =
Rails.root.join('config', 'unsubscribe.csv')

Class Method Summary collapse

Class Method Details

.all_active_usersObject


10
11
12
13
14
15
16
17
18
# File 'lib/csv_generator.rb', line 10

def self.all_active_users
  file = self.filename("all_active_users")
  sql = "    SELECT email AS '%EMAIL%' \n      \#{self.output_syntax(file)}\n      FROM users where username IS NOT NULL\n"
  ActiveRecord::Base.connection.execute(sql)
end

.all_inactive_invited_usersObject


20
21
22
23
24
25
26
27
28
29
30
31
32
# File 'lib/csv_generator.rb', line 20

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

.all_usersObject


44
45
46
47
48
49
50
# File 'lib/csv_generator.rb', line 44

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

.backer_email_conditionObject


152
153
154
155
156
# File 'lib/csv_generator.rb', line 152

def self.backer_email_condition
  b_emails = self.backer_emails
  b_emails.map!{|a| "'#{a}'"}
  "`users`.`email` IN (#{query_string_from_array(b_emails[1..b_emails.length])})" 
end

.backer_emailsObject


237
238
239
# File 'lib/csv_generator.rb', line 237

def self.backer_emails
  self.backers.map{|b| b[0]}
end

.backersObject


233
234
235
# File 'lib/csv_generator.rb', line 233

def self.backers
  self.load_waiting_list_csv(BACKER_CSV_LOCATION)
end

.backers_never_loginObject


68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
# File 'lib/csv_generator.rb', line 68

def self.
  #IF(`users`.invitation_token,   ,NULL)
  file = self.filename("v3_backers_never_login.csv")
  sql = "        SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'\n        UNION\n          SELECT `users`.email AS '%EMAIL%',\n                  'Friend of Diaspora*' AS '%NAME%',\n              CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) AS '%INVITATION_LINK%'\n              \#{self.output_syntax(file)}\n           FROM `users`\n          WHERE \#{self.has_email} AND \#{self.has_invitation_token} AND \#{self.backer_email_condition} AND \#{self.unsubscribe_email_condition} AND \#{self.never_login_query};\n"

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

.backers_old_loginObject


60
61
62
63
64
65
66
# File 'lib/csv_generator.rb', line 60

def self.
  file = self.filename("v2_backers_old_login.csv")
  sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition} " +
                              " AND #{self.unsubscribe_email_condition} AND #{self.old_login_query}")

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

.backers_recent_loginObject


52
53
54
55
56
57
58
# File 'lib/csv_generator.rb', line 52

def self.
  file = self.filename("v1_backers_recent_login.csv")
  sql = self.select_fragment(file, "#{self.has_email} AND #{self.backer_email_condition}" +
                              " AND #{self.unsubscribe_email_condition} AND #{self.recent_login_query}")

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

.filename(name) ⇒ Object


211
212
213
# File 'lib/csv_generator.rb', line 211

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

.generate_csvsObject


34
35
36
37
38
39
40
41
42
# File 'lib/csv_generator.rb', line 34

def self.generate_csvs
  #`mkdir /tmp/csvs`
  self.
  self.
  self.
  self.
  self.
  self.
end

.has_emailObject


148
149
150
# File 'lib/csv_generator.rb', line 148

def self.has_email
  '`users`.`email` IS NOT NULL AND `users`.`email` != ""'
end

.has_invitation_tokenObject


144
145
146
# File 'lib/csv_generator.rb', line 144

def self.has_invitation_token
  '`users`.`invitation_token` IS NOT NULL'
end

.has_usernameObject


140
141
142
# File 'lib/csv_generator.rb', line 140

def self.has_username
  '`users`.`username` IS NOT NULL'
end

.load_waiting_list_csv(filename) ⇒ Object

—————- HELPER METHODS ————————-


199
200
201
202
203
204
# File 'lib/csv_generator.rb', line 199

def self.load_waiting_list_csv(filename)
  require 'csv'
  csv = filename
  people = CSV.read(csv)
  people
end

.never_login_queryObject


178
179
180
# File 'lib/csv_generator.rb', line 178

def self.
  "(last_sign_in_at IS NULL)"
end

.non_backer_email_conditionObject


158
159
160
161
162
# File 'lib/csv_generator.rb', line 158

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

.non_backers_logged_inObject


116
117
118
119
120
121
122
# File 'lib/csv_generator.rb', line 116

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

.non_backers_never_loginObject


101
102
103
104
105
106
107
108
109
110
111
112
113
114
# File 'lib/csv_generator.rb', line 101

def self.
  file = self.filename("v6_non_backers_never_login.csv")
  sql = "        SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'\n        UNION\n          SELECT `users`.email AS '%EMAIL%',\n                  'Friend of Diaspora*' AS '%NAME%',\n              CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) AS '%INVITATION_LINK%'\n              \#{self.output_syntax(file)}\n           FROM `users`\n          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};\n"
  ActiveRecord::Base.connection.execute(sql)
end

.non_backers_old_loginObject


93
94
95
96
97
98
99
# File 'lib/csv_generator.rb', line 93

def self.
  file = self.filename("v5_non_backers_old_login.csv")
  sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} " +
                              "AND #{self.unsubscribe_email_condition} AND #{self.old_login_query}")

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

.non_backers_recent_loginObject


85
86
87
88
89
90
91
# File 'lib/csv_generator.rb', line 85

def self.
  file = self.filename("v4_non_backers_recent_login.csv")
  sql = self.select_fragment(file, "#{self.has_email} AND #{self.non_backer_email_condition} " +
                              "AND #{self.unsubscribe_email_condition} AND #{self.recent_login_query}")

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

.offsetObject


206
207
208
209
# File 'lib/csv_generator.rb', line 206

def self.offset
  offset_filename = OFFSET_LOCATION
  File.read(offset_filename).to_i
end

.old_login_queryObject


174
175
176
# File 'lib/csv_generator.rb', line 174

def self.
  "(last_sign_in_at < SUBDATE(NOW(), INTERVAL 31 DAY))"
end

.output_syntax(filename) ⇒ Object


215
216
217
218
219
220
221
# File 'lib/csv_generator.rb', line 215

def self.output_syntax filename
  "  INTO OUTFILE '\#{filename}'\nFIELDS TERMINATED BY '\\t'\nLINES TERMINATED BY '\\n'\n"
end

.query_string_from_array(array) ⇒ Object


182
183
184
# File 'lib/csv_generator.rb', line 182

def self.query_string_from_array(array)
  array.join(", ")
end

.recent_login_queryObject


170
171
172
# File 'lib/csv_generator.rb', line 170

def self.
  "(last_sign_in_at > SUBDATE(NOW(), INTERVAL 31 DAY))"
end

.select_fragment(file, where_clause) ⇒ Object

—————- QUERY METHODS & NOTES ————————-


125
126
127
128
129
130
131
132
133
134
135
136
137
138
# File 'lib/csv_generator.rb', line 125

def self.select_fragment(file, where_clause)
  sql = "        SELECT '%EMAIL%','%NAME%','%INVITATION_LINK%'\n        UNION\n          SELECT `users`.email AS '%EMAIL%',\n                 IF( `profiles`.first_name IS NOT NULL AND `profiles`.first_name != \"\",\n                                             `profiles`.first_name, 'Friend of Diaspora*') AS '%NAME%',\n              IF(`users`.invitation_token, CONCAT( 'https://joindiaspora.com/users/invitation/accept?invitation_token=', `users`.invitation_token) ,NULL) AS '%INVITATION_LINK%'\n              \#{self.output_syntax(file)}\n           FROM `users`\n           JOIN `people` ON `users`.id = `people`.owner_id JOIN `profiles` ON `people`.id = `profiles`.person_id\n          WHERE \#{where_clause};\n"
end

.unsubscribe_email_conditionObject


164
165
166
167
168
# File 'lib/csv_generator.rb', line 164

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

.unsubscriber_emailsObject


245
246
247
# File 'lib/csv_generator.rb', line 245

def self.unsubscriber_emails
  self.unsubsribers.map{|b| b[1]}
end

.unsubsribersObject


241
242
243
# File 'lib/csv_generator.rb', line 241

def self.unsubsribers
  self.load_waiting_list_csv(UNSUBSCRIBE_LOCATION)
end

.waitlistObject


223
224
225
226
227
228
229
230
231
# File 'lib/csv_generator.rb', line 223

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