· 4 years ago · Mar 18, 2021, 09:56 PM
1require "digest"
2
3class TargetGroupStep1Data
4 def initialize( target_group )
5 @target_group = target_group
6 @query_criterion_values = query_criterion_values
7
8 @os_id_table_name = @target_group.os_id_table_name
9 @dm_id_table_name = @target_group.dm_id_table_name
10 end
11
12 def create_data use_cache = true
13 cache_content = "locked#{@target_group.locked}fixed#{@target_group.fixed}"
14 @query_criterion_values.each do |qcv|
15 cache_content += "#{qcv.criterion.name}#{qcv.values.join('')}#{qcv.relation}#{qcv.prioritized}#{qcv.criterion_value.sub_name}"
16 end
17
18 # If there is cache item with the query criterion value content,
19 # the data has already been created. This happens when landing on
20 # edit page, for example, and when repeatedly hitting
21 # 'refresh' button while editing target group.
22 cache_key = @target_group.uid + "_query_criterion_values"
23
24 return if Rails.cache.read(cache_key) == cache_content && table_exists?(@os_id_table_name) && table_exists?(@dm_id_table_name) && !is_updating_os_temp_table_needed? && use_cache
25
26 created = false
27
28 # Check if we can copy data over from session storage. This happens
29 # when user is creating a new target group, hits 'refresh' and, after
30 # that, 'save' without modifying the criterion.
31 if !@target_group.id.nil? && use_cache
32 session_cache_key = "#{TargetGroup.session_id}_query_criterion_values"
33
34 session_os_ids_table_name = "tg_#{@target_group.session_uid}_os_ids"
35 session_dm_ids_table_name = "tg_#{@target_group.session_uid}_dm_ids"
36
37 if Rails.cache.read(session_cache_key) == cache_content && table_exists?(session_os_ids_table_name) && table_exists?(session_dm_ids_table_name)
38 rename_tables([
39 [session_os_ids_table_name, @os_id_table_name],
40 [session_dm_ids_table_name, @dm_id_table_name]])
41
42 Rails.cache.delete(session_cache_key)
43 Rails.cache.delete("table_exists_#{session_os_ids_table_name}")
44 Rails.cache.delete("table_exists_#{session_dm_ids_table_name}")
45 created = true
46 end
47 end
48
49 if !created
50 if person_data_required?
51 create_tables_with_person_data_required
52 else
53 create_tables
54 end
55 end
56
57 Rails.cache.write(cache_key, cache_content, :expires_in => 1.day) if (@target_group.status != TargetGroup::STATUS_JUST_CREATED_UNSAVED && use_cache)
58 end
59
60
61
62 def rename_tables(renames)
63 rename_statements = []
64
65 renames.each do |rename|
66 ActiveRecord::Base.connection.execute("DROP TABLE IF EXISTS #{rename[1]}")
67 rename_statements << "#{rename[0]} TO #{rename[1]}"
68 end
69
70 ActiveRecord::Base.connection.execute("RENAME TABLE #{rename_statements.join(', ')}")
71 end
72
73 def create_dm_data
74 create_matching_dm_table
75 end
76
77 def clean_data
78 truncate_data_from_os
79 end
80
81 def create_dm_data_with_office_sites
82 select_sql = "SELECT DISTINCT os.office_site_id, dm.decision_maker_id
83 FROM #{@os_id_table_name} os
84 LEFT JOIN decision_maker_office_sites dmos ON dmos.office_site_id = os.office_site_id
85 INNER JOIN #{@dm_id_table_name} dm ON dm.decision_maker_id = dmos.decision_maker_id"
86
87 create_table_with_load_data "#{@dm_id_table_name}_with_os_ids", # table name
88 ['office_site_id', 'decision_maker_id'], # columns
89 ['office_site_id'], # index
90 select_sql
91 end
92
93 def append_office_sites office_sites_ids
94 ActiveRecord::Base.connection.execute("INSERT INTO #{@os_id_table_name}(office_site_id) VALUES(#{office_sites_ids.join('),(')})") if office_sites_ids
95 end
96
97 def append_to_session_storage
98 if !table_exists?(@os_id_table_name) || !table_exists?(@dm_id_table_name)
99 Rails.cache.delete(@target_group.uid + "_query_criterion_values")
100 create_data()
101 end
102
103 copy_os_sql = "SELECT office_site_id FROM #{@os_id_table_name}"
104 copy_dm_sql = "SELECT decision_maker_id FROM #{@dm_id_table_name}"
105
106 create_tg_os_or_dm_table "tg_#{@target_group.session_uid}_os_ids", copy_os_sql, 'office_site_id'
107 create_tg_os_or_dm_table "tg_#{@target_group.session_uid}_dm_ids", copy_dm_sql, 'decision_maker_id'
108 end
109
110 def os_related_critrion_values
111 ret_val = []
112 @query_criterion_values.each do |query_criterion_value|
113 if query_criterion_value.is_office_site_criterion_value?
114 ret_val << query_criterion_value
115 end
116 end
117 ret_val
118 end
119
120 def restricted_decision_maker_ids(target_group)
121 tg_ids = restricted_target_group_ids(target_group)
122 dm_ids = []
123 tg_ids.each { |id| dm_ids << target_group_dm_ids(id) }
124 return dm_ids
125 end
126
127 def table_exists?(name)
128 return true if Rails.cache.read("table_exists_#{name}")
129 database = ActiveRecord::Base.connection.current_database
130 table_count_sql = "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = '#{database}' AND table_name = '#{name}'"
131 value = ActiveRecord::Base.connection.select_value(table_count_sql)
132 exists = value > 0
133 Rails.cache.write("table_exists_#{name}", 1, :expires_in => 5.minutes) if exists
134 return exists
135 end
136
137 def table_empty?(name)
138 return true if Rails.cache.read("table_empty_#{name}")
139 count_sql = "SELECT COUNT(*) FROM #{name}"
140 empty = false
141 ActiveRecord::Base.connection.uncached do
142 empty = ActiveRecord::Base.connection.select_value(count_sql) == 0
143 end
144
145 Rails.cache.write("table_empty_#{name}", 1, :expires_in => 5.minutes) if empty
146 return empty
147 end
148
149
150 private
151
152 def get_count_for_fixed_or_locked_target_group type
153 "SELECT COUNT(*) FROM #{type.to_s}_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
154 end
155
156 def is_updating_os_temp_table_needed?
157
158 if @target_group.locked?
159 sql = get_count_for_fixed_or_locked_target_group :locked
160 elsif @target_group.fixed?
161 sql = get_count_for_fixed_or_locked_target_group :fixed
162 end
163
164 if !sql.nil?
165 result = ActiveRecord::Base.connection.execute(sql)
166 number_of_office_sites = result.first[0]
167
168 if @target_group.office_site_count < number_of_office_sites
169 return true
170 end
171 end
172
173
174 return false
175 end
176
177 def create_tables(temp_os_id_table_name = nil)
178 os_table_for_dms = create_matching_os_table( temp_os_id_table_name )
179 create_matching_dm_table( os_table_for_dms )
180 @query_criterion_values.map { |criterion| criterion.is_free_text_phone_or_email? ? insert_free_text_phone_or_email_dms(criterion) : '' }
181 insert_original_decision_makers( os_table_for_dms ) if !@target_group.id.nil?
182 end
183
184 def create_tables_with_person_data_required
185 temp_os_id_table_name = "#{@os_id_table_name}_#{random_prefix}"
186 create_tables(temp_os_id_table_name)
187 remake_matching_os_table_with_pdr(temp_os_id_table_name)
188 create_matching_dm_table if @target_group.fixed
189 insert_original_decision_makers if !@target_group.id.nil?
190 end
191
192 def copy_target_group_decision_makers(copy_name)
193 sql = "CREATE TABLE #{copy_name} SELECT decision_maker_id FROM decision_makers_target_groups
194 WHERE target_group_id = #{@target_group.id}"
195 ActiveRecord::Base.connection.execute(sql)
196 end
197
198 def drop_tables tables = []
199 tables.each { |table| sql = "DROP TABLE IF EXISTS #{table}"
200 ActiveRecord::Base.connection.execute(sql) }
201 end
202
203 def create_matching_os_table(os_id_table_name = nil)
204 os_id_table_name ||= @os_id_table_name
205 select_sql = sql_select_for_matching_os()
206 if @target_group.locked || @target_group.fixed || select_sql.blank?
207 create_tg_os_or_dm_table(os_id_table_name, select_sql, "office_site_id")
208 return os_id_table_name
209 else
210 # Check if we already have a table with results for the given criteria...
211 sql_digest = Digest::MD5.hexdigest(select_sql)
212 criteria_table_name = "fromcriteria_os_ids_#{sql_digest}"
213 # If we don't, create and populate one...
214 # Because these tables are in-memory tables they might exist but be empty after a server restart
215 unless table_exists?(criteria_table_name) && !table_empty?(criteria_table_name) && Rails.cache.read("criteria_cached_#{criteria_table_name}")
216 create_tg_os_or_dm_table(criteria_table_name, select_sql, "office_site_id", false)
217 Rails.cache.write("criteria_cached_#{criteria_table_name}", 1, :expires_in => 12.hours)
218 end
219
220 # Populate the tg specific table from the criteria table
221 create_tg_os_or_dm_table(os_id_table_name, "SELECT office_site_id FROM #{criteria_table_name}", "office_site_id")
222
223 return criteria_table_name
224 end
225 end
226
227 def create_matching_dm_table(os_id_table_name = nil)
228 os_id_table_name ||= @os_id_table_name
229 select_sql = sql_select_for_matching_dm(os_id_table_name)
230
231 if select_sql.nil? && !@target_group.id.nil?
232 select_sql = "SELECT DISTINCT decision_maker_id FROM decision_makers_target_groups WHERE target_group_id = #{@target_group.id}"
233 create_tg_os_or_dm_table(@dm_id_table_name, [select_sql], "decision_maker_id")
234
235 elsif @target_group.locked || @target_group.fixed || select_sql.blank?
236 create_tg_os_or_dm_table(@dm_id_table_name, [select_sql], "decision_maker_id")
237 return @dm_id_table_name
238 else
239 # Check if we already have a table with results for the given criteria and that it's not empty...
240 sql_digest = Digest::MD5.hexdigest(select_sql)
241 criteria_table_name = "fromcriteria_dm_ids_#{sql_digest}"
242 # If we don't, create and populate one...
243 unless table_exists?(criteria_table_name) && !table_empty?(criteria_table_name) && Rails.cache.read("criteria_cached_#{criteria_table_name}")
244 create_tg_os_or_dm_table(criteria_table_name, select_sql, "decision_maker_id", false)
245 Rails.cache.write("criteria_cached_#{criteria_table_name}", 1, :expires_in => 12.hours)
246 end
247 # Populate the tg specific table from the criteria table
248
249 create_tg_os_or_dm_table(@dm_id_table_name, ["SELECT decision_maker_id FROM #{criteria_table_name}"], "decision_maker_id")
250 end
251 end
252
253 def remake_matching_os_table_with_pdr(temporary_os_id_table)
254 sql = "SELECT DISTINCT os.office_site_id
255 FROM #{temporary_os_id_table} os
256 JOIN decision_maker_office_sites dmos ON dmos.office_site_id = os.office_site_id
257 JOIN #{@dm_id_table_name} dm ON dmos.decision_maker_id = dm.decision_maker_id"
258
259 create_tg_os_or_dm_table(@os_id_table_name, sql, "office_site_id")
260 end
261
262 def insert_original_decision_makers(os_id_table_name = nil)
263 os_id_table_name ||= @os_id_table_name
264 sql = "INSERT INTO #{@dm_id_table_name} (decision_maker_id)
265 SELECT dmtg.decision_maker_id FROM decision_makers_target_groups dmtg
266 JOIN decision_maker_office_sites dmos ON dmos.decision_maker_id = dmtg.decision_maker_id
267 JOIN #{os_id_table_name} oitn ON oitn.office_site_id = dmos.office_site_id
268 WHERE dmtg.target_group_id = #{@target_group.id}
269 AND dmtg.decision_maker_id NOT IN (SELECT * FROM #{@dm_id_table_name})
270 AND dmos.office_site_id IN (SELECT * FROM #{os_id_table_name})
271 #{has_dm_restriction_list_criteria?(os_id_table_name) ? "AND dmtg.decision_maker_id NOT IN (SELECT decision_maker_id from decision_makers_target_groups where target_group_id IN (#{restricted_dm_tg_ids}))" : ''}"
272
273 result = ActiveRecord::Base.connection.execute(sql)
274 end
275
276 def insert_free_text_phone_or_email_dms(criterion)
277 values = criterion.values.map { |value| value.gsub(/\s+/, "") }
278 sql = "INSERT INTO #{@dm_id_table_name} (decision_maker_id)
279 SELECT DISTINCT(decision_maker_id) FROM search_all_values
280 WHERE field = '#{criterion.criterion_value.sub_name}' AND decision_maker_id IS NOT NULL
281 AND decision_maker_id NOT IN (SELECT * FROM #{@dm_id_table_name})
282 AND (value LIKE ('30##{values.join("%') OR value LIKE ('30#")}%'))
283 "
284 ActiveRecord::Base.connection.execute(sql)
285 end
286
287 def dm_restriction_lists
288 wheres = []
289 ids = restricted_target_group_ids(@target_group)
290 ids.each { |id| wheres << dm_restriction_list_where(id) }
291 wheres
292 end
293
294 def restricted_target_group_ids(target_group)
295 restricted_tg_ids = []
296 target_group.criterion_values.each { |cv| restricted_tg_ids = cv.dm_restriction_list_tg_ids if cv.is_dm_restriction_list_criteria? }
297 unless restricted_tg_ids.empty?
298 restricted_tg_ids.map! { |id| id.to_i }
299 end
300 restricted_tg_ids
301 end
302
303 def dm_restriction_list_where(id)
304 "AND dm.decision_maker_id NOT IN (SELECT decision_maker_id FROM decision_makers_target_groups WHERE target_group_id = #{id})"
305 end
306
307 def target_group_dm_ids(tg_id)
308 dm_ids = []
309 sql = "SELECT decision_maker_id FROM decision_makers_target_groups WHERE target_group_id=#{tg_id}"
310 result = ActiveRecord::Base.connection.execute(sql)
311 result.each { |id| dm_ids << id.first }
312 dm_ids
313 end
314
315 def sql_select_for_matching_os
316 select_sqls = []
317 os_related_critrion_values.each do |query_criterion_value|
318 select_sqls << query_criterion_value.os_table_select_query
319 if os_related_critrion_values.size == 1 && query_criterion_value.is_exclusive_criterion?
320 select_sqls << query_criterion_value.os_all_records_select
321 end
322 end
323
324 if select_sqls.empty?
325 os_ids_if_no_criteria_in_tg
326 else
327 if @target_group.locked
328 select_locked_os
329 elsif @target_group.fixed
330 "SELECT t.obj_id as office_site_id
331 FROM (
332 #{select_sqls.join(' UNION ALL ')}
333 ) t
334 #{join_fixed_or_locked_os}
335 #{ @target_group.has_enabled_office_sites? ? "WHERE t.obj_id IN (SELECT * FROM #{@os_id_table_name})" :
336 "WHERE t.obj_id IN (SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id=#{@target_group.id})"}
337 #{ additional_where_clauses_for_os }
338 GROUP BY t.obj_id
339
340 HAVING
341 COUNT(DISTINCT t.exclusive_attribute_id) = 0
342 AND COUNT(DISTINCT t.inclusive_attribute_id) = #{os_distinct_attribute_count}"
343 else
344 "SELECT t.obj_id as office_site_id
345 FROM (
346 #{select_sqls.join(' UNION ALL ')}
347 ) t
348 #{ additional_where_clauses_for_os }
349 GROUP BY t.obj_id
350
351 HAVING
352 COUNT(DISTINCT t.exclusive_attribute_id) = 0
353 AND COUNT(DISTINCT t.inclusive_attribute_id) = #{os_distinct_attribute_count}"
354 end
355 end
356 end
357
358 def additional_where_clauses_for_dm
359 where_clause = []
360 prefix = "WHERE "
361
362 if @target_group.has_email_criteria?
363 where_clause << email_criteria_where
364 end
365
366 unless where_clause.empty?
367 where_clause.size > 1 ? prefix + where_clause.join(' AND ') : prefix + where_clause.to_s
368 else
369 ''
370 end
371
372 end
373
374 def additional_where_clauses_for_os
375 where_clause = []
376 prefix = prefix_for_additional_os_where_clause
377
378 if @target_group.has_financial_criteria?
379 where_clause << financial_criteria_where
380 end
381
382 unless where_clause.empty?
383 where_clause.size > 1 ? prefix + where_clause.join(' AND ') : prefix + where_clause.to_s
384 else
385 ''
386 end
387
388 end
389
390 def prefix_for_additional_os_where_clause
391 @target_group.fixed ? "AND " : "WHERE "
392 end
393
394 def email_criteria_where
395 "t.obj_id IN (SELECT id from decision_makers where email != '')"
396 end
397
398 def financial_criteria_where
399 "t.obj_id IN (SELECT office_site_id FROM financial_figures WHERE #{criteria_not_null_sql} AND recent=1)"
400 end
401
402 def criteria_not_null_sql
403 criteria = []
404 @target_group.criterion_values.each { |value| if value.is_financial_criteria?
405 criteria << "#{value.db_search_name} IS NOT NULL"
406 end
407 }
408 criteria.join(' AND ').to_s
409 end
410
411 def sql_select_for_matching_dm(os_id_table_name = nil)
412 os_id_table_name ||= @os_id_table_name
413
414 select_sqls = []
415 dm_related_critrion_values(os_id_table_name).each do |query_criterion_value|
416 select_sqls << query_criterion_value.dm_table_select_query unless (@target_group.fixed && query_criterion_value.is_dm_restriction_list_criteria?)
417 if dm_related_critrion_values.size == 1 && query_criterion_value.is_dm_restriction_list_criteria? && query_criterion_value.is_exclusive_criterion? && !@target_group.locked && !@target_group.fixed
418 select_sqls << query_criterion_value.dm_all_records_select
419 end
420 end
421
422 select_sqls.empty? ? dm_ids_if_no_criteria_in_tg(os_id_table_name) :
423
424 "SELECT t.obj_id as decision_maker_id
425 FROM (
426 #{@target_group.fixed? ? select_sqls.join(' UNION DISTINCT ') : select_sqls.join(' UNION ALL ')}
427 ) t
428 JOIN decision_maker_office_sites dmos ON dmos.decision_maker_id = t.obj_id
429 JOIN #{os_id_table_name} ON dmos.office_site_id = #{os_id_table_name}.office_site_id
430 #{join_dms_for_fixed_or_locked_os}
431 #{ additional_where_clauses_for_dm }
432 GROUP BY t.obj_id
433 HAVING
434 COUNT(DISTINCT t.exclusive_attribute_id) = 0
435 AND COUNT(DISTINCT t.inclusive_attribute_id) = #{dm_distinct_attribute_count}".gsub(/\n/, "")
436 end
437
438 def has_dm_restriction_list_criteria?(os_id_table_name)
439 dm_related_critrion_values(os_id_table_name).each do |value|
440 if value.is_dm_restriction_list_criteria?
441 return true
442 end
443 end
444 return false
445 end
446
447 def truncate_data_from_os
448 if table_exists?(@os_id_table_name)
449 ActiveRecord::Base.connection.execute("TRUNCATE TABLE #{@os_id_table_name}")
450 end
451 if table_exists?(@dm_id_table_name)
452 ActiveRecord::Base.connection.execute("TRUNCATE TABLE #{@dm_id_table_name}")
453 end
454 end
455
456 def dynamic_added_decision_makers
457 if !@target_group.new_record?
458 "SELECT decision_maker_id FROM decision_makers_target_groups WHERE target_group_id = #{@target_group.id}"
459 end
460 end
461
462 def dynamic_added_office_sites
463 if !@target_group.new_record?
464 #sql="SELECT COUNT(*) FROM locked_target_group_office_sites WHERE target_group_id=#{@target_group.id} UNION SELECT COUNT(*) FROM fixed_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
465 #rslt=ActiveRecord::Base.connection.execute(sql)
466 #puts "****rslt***"
467 #p rslt.first
468 "SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id=#{@target_group.id} UNION SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
469 end
470=begin
471 if @target_group.locked?
472 "SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
473 elsif @target_group.fixed?
474 "SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
475 elsif @target_group.locked? && @target_group.fixed?
476 "SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id=#{@target_group.id} UNION SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id=#{@target_group.id}"
477 end
478=end
479 end
480
481 def select_locked_os
482 "SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id = #{@target_group.id}"
483 end
484
485 def join_fixed_or_locked_os
486 if @target_group.locked
487 "JOIN locked_target_group_office_sites fixed_os ON (fixed_os.target_group_id = #{@target_group.id} AND fixed_os.office_site_id = t.obj_id)"
488 elsif @target_group.fixed
489 "JOIN fixed_target_group_office_sites fixed_os ON (fixed_os.target_group_id = #{@target_group.id} AND fixed_os.office_site_id = t.obj_id)"
490 end
491 end
492
493 def union_fixed_or_locked_os
494 if @target_group.locked
495 "UNION SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id = #{@target_group.id}"
496 elsif @target_group.fixed
497 "UNION SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id = #{@target_group.id}"
498 end
499 end
500
501 def join_dms_for_fixed_or_locked_os
502 if @target_group.locked
503 "JOIN locked_target_group_office_sites locked_os
504 ON (locked_os.target_group_id = #{@target_group.id} AND locked_os.office_site_id = dmos.office_site_id)"
505 elsif @target_group.fixed
506 "JOIN fixed_target_group_office_sites fixed_os
507 ON (fixed_os.target_group_id = #{@target_group.id} AND fixed_os.office_site_id = dmos.office_site_id)"
508 end
509 end
510
511 def os_ids_if_no_criteria_in_tg
512 if @target_group.locked
513 "SELECT office_site_id FROM locked_target_group_office_sites WHERE target_group_id = #{@target_group.id}"
514 elsif @target_group.fixed
515 "SELECT office_site_id FROM fixed_target_group_office_sites WHERE target_group_id = #{@target_group.id}"
516 else
517 "SELECT id AS office_site_id FROM office_sites"
518 end
519 end
520
521 def dm_ids_if_no_criteria_in_tg(os_id_table_name = nil)
522 os_id_table_name ||= @os_id_table_name
523 if @target_group.id && ( @target_group.fixed || (@target_group.locked && @target_group.fixed) )
524 "SELECT DISTINCT dmos.decision_maker_id
525 FROM #{os_id_table_name}
526 JOIN decision_maker_office_sites dmos ON dmos.office_site_id = #{os_id_table_name}.office_site_id
527 JOIN decision_makers_target_groups dmtg ON ( dmos.decision_maker_id = dmtg.decision_maker_id AND dmtg.target_group_id = #{@target_group.id})
528 #{has_dm_restriction_list_criteria?(os_id_table_name) ? "WHERE dmos.decision_maker_id NOT IN (SELECT decision_maker_id from decision_makers_target_groups where target_group_id IN (#{restricted_dm_tg_ids}))" : ''}"
529 elsif @target_group.id && (@target_group.locked)
530 "SELECT decision_maker_id FROM decision_makers_target_groups WHERE target_group_id = #{@target_group.id}"
531 else
532 nil
533 end
534 end
535
536 def restricted_dm_tg_ids
537 ids = restricted_target_group_ids(@target_group).join(',')
538 end
539
540 def os_map_table_name
541 include_numeric_criteria? ? 'os_num_attribute_maps' : 'os_attribute_maps'
542 end
543
544 def dm_map_table_name
545 'dm_attribute_maps'
546 end
547
548 def include_numeric_criteria?
549 @target_group.criterion_values.each do |cv|
550 return true if ['fin_oper_ratio', 'fin_solvency_ratio', 'fin_turnover_change', 'fin_turnover_per_person', 'year_est'].include?(cv.criterion.name)
551 end
552 false
553 end
554
555 def query_criterion_values(join_to = nil)
556 ret_val = []
557 @target_group.criterion_values.each do |criterion_value|
558 ret_val << QueryCriterionValue.new(criterion_value, join_to) if criterion_value.criterion
559 end unless @target_group.criterion_values.blank?
560 ret_val
561 end
562
563 def dm_related_critrion_values(join_to = nil)
564 ret_val = []
565 query_criterion_values(join_to).each do |query_criterion_value|
566 if query_criterion_value.is_decision_maker_criterion_value?
567 ret_val << query_criterion_value
568 end
569 end
570 ret_val
571 end
572
573 def dm_distinct_attribute_count
574 count = dm_related_critrion_values.size
575
576 #Special case with Position and Responsibility area classification. It should be treated as one.
577 is_position_classif = false
578 is_ra_classif = false
579 dm_related_critrion_values.each do |query_criterion_value|
580 is_position_classif = true if query_criterion_value.is_position_criteria?
581 is_ra_classif = true if query_criterion_value.is_responsibility_area_criteria?
582 if query_criterion_value.is_exclusive_criterion?
583 count -= 1
584 if dm_related_critrion_values.size == 1
585 count += 1
586 end
587 end
588 end
589 if is_position_classif && is_ra_classif
590 count -= 1
591 end
592 count
593 end
594
595 def os_distinct_attribute_count
596 count = os_related_critrion_values.size
597
598 #Special case with Fonecta and TOL classification. It should be treated as one.
599 is_fonecta_classif = false
600 is_tol_classif = false
601 fonecta_tol_counter = 0
602 os_related_critrion_values.each do |query_criterion_value|
603 fonecta_tol_counter += 1 if query_criterion_value.is_fonecta_classification_criteria?
604 fonecta_tol_counter += 1 if query_criterion_value.is_tol_classification_criteria?
605 if query_criterion_value.is_exclusive_criterion?
606 count -= 1
607 if os_related_critrion_values.size == 1
608 count += 1
609 end
610 end
611 end
612 if fonecta_tol_counter > 0
613 count -= (fonecta_tol_counter - 1)
614 end
615 count
616 end
617
618 def create_tg_os_or_dm_table(table_name, select_sqls, column_name, update_list = true)
619 outfile_path = Utils.gen_outfile_path()
620
621 select_sql = if select_sqls.is_a?(Array)
622 select_sqls.compact.join(" UNION ")
623 else
624 select_sqls
625 end
626
627 select_into_sql = "#{select_sql} INTO OUTFILE '#{outfile_path}'"
628 insert_sql = "LOAD DATA INFILE '#{outfile_path}' IGNORE INTO TABLE #{table_name}"
629 drop_sql = "DROP TABLE IF EXISTS #{table_name}"
630 create_sql = "CREATE TABLE IF NOT EXISTS #{table_name} (#{column_name} INT(11) ) ENGINE = MEMORY"
631 index_sql = "CREATE INDEX #{table_name}_#{random_prefix}_i ON #{table_name} (#{column_name})"
632
633 file_exists = false
634 if !select_sql.blank? && table_exists?(table_name)
635 ActiveRecord::Base.connection.execute(select_into_sql)
636 file_exists = true
637 end
638
639 truncated = false
640 if table_exists?(table_name)
641 ActiveRecord::Base.connection.execute("TRUNCATE TABLE #{table_name}")
642 truncated = true
643 end
644
645 if update_list
646 if ListTgTable.find_by_table_name(table_name)
647 ListTgTable.update_table_usage(@target_group)
648 else
649 ListTgTable.add_table({ :table_name => table_name, :target_group_id => @target_group.id || 0, :table_type => column_name })
650 end
651 end
652
653 ActiveRecord::Base.connection.execute(select_into_sql) if file_exists == false && !select_sql.blank?
654 ActiveRecord::Base.connection.execute(drop_sql) unless truncated
655 ActiveRecord::Base.connection.execute(create_sql) unless truncated
656 ActiveRecord::Base.connection.execute(insert_sql) if !select_sql.blank?
657 ActiveRecord::Base.connection.execute(index_sql) unless truncated
658
659 Rails.cache.write("table_exists_#{table_name}", 1, :expires_in => 5.minutes)
660 end
661
662 def person_data_required?
663 @target_group.is_criteria_outlines_companies_mode_enabled?
664 end
665
666 def random_prefix
667 o = [('a'..'z'),('A'..'Z')].map{|i| i.to_a}.flatten
668 return (0..5).map{ o[rand(o.length)] }.join
669 end
670
671 def create_table_with_load_data table_name, columns, indexes, select_sql
672 outfile_path = Utils.gen_outfile_path()
673
674 select_into_sql = "#{select_sql} INTO OUTFILE '#{outfile_path}'"
675 insert_sql = "LOAD DATA INFILE '#{outfile_path}' INTO TABLE #{table_name}"
676 drop_sql = "DROP TABLE IF EXISTS #{table_name}"
677 columns_sql = columns.map { |col| "#{col} INT(11)" }.join(', ')
678 create_sql = "CREATE TABLE #{table_name} (#{columns_sql}) ENGINE = MEMORY"
679
680 index_sql = "CREATE INDEX #{table_name}_#{random_prefix}_i ON #{table_name} (#{indexes.join(', ')})"
681
682 #puts insert_sql
683 if ListTgTable.find_by_table_name(table_name)
684 ListTgTable.update_table_usage(@target_group)
685 else
686 ListTgTable.add_table({ :table_name => table_name, :target_group_id => @target_group.id || 0, :table_type => columns.join('_')})
687 end
688
689 ActiveRecord::Base.connection.execute(select_into_sql) if !select_sql.blank?
690 ActiveRecord::Base.connection.execute(drop_sql)
691 ActiveRecord::Base.connection.execute(create_sql)
692 ActiveRecord::Base.connection.execute(insert_sql) if !select_sql.blank?
693 ActiveRecord::Base.connection.execute(index_sql)
694 end
695
696
697end