Module: SystematicReviewViewsMixin

Included in:
SystematicReview
Defined in:
model/systematic_review_views_mixin.rb

Overview

Mixin for methods to view and create views related to systematic reviews

Instance Method Summary collapse

Instance Method Details

#bib_references ⇒ Object 



215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
 
# File 'model/systematic_review_views_mixin.rb', line 215

def bib_references
  view_name = bib_references_tn
  if @bib_references_tn_exists.nil?
    @bib_references_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT refs.id, refs.text, refs.doi, refs.canonical_document_id,
COUNT(DISTINCT(r.canonical_document_id)) as cited_by_cd_n,
COUNT(DISTINCT(s.id)) as searches_count
FROM bib_references refs
INNER JOIN records_references rr ON refs.id = rr.reference_id
INNER JOIN records r ON rr.record_id=r.id
INNER JOIN records_searches br ON r.id=br.record_id
INNER JOIN searches s ON br.search_id=s.id WHERE s.systematic_review_id=#{self[:id]} GROUP BY refs.id")
    end
  end
  $db[view_name.to_sym]
end

#bib_references_tn ⇒ Object 



232
233
234
 
# File 'model/systematic_review_views_mixin.rb', line 232

def bib_references_tn
  "sr_#{self[:id]}_bib_references"
end

#cd_all_id_table ⇒ Object 



86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
 
# File 'model/systematic_review_views_mixin.rb', line 86

def cd_all_id_table
  view_name = cd_all_id_table_tn
  if @cd_all_id_table_exists.nil?
    @cd_all_id_table_exists=true
    cd_record_id_table
    cd_reference_id_table
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS
    SELECT cd_id FROM #{cd_record_id_table_tn}
  UNION
  SELECT cd_id FROM #{cd_reference_id_table_tn}
")
    end
  end
  $db[view_name.to_sym]
end

#cd_all_id_table_tn ⇒ Object 



82
83
84
 
# File 'model/systematic_review_views_mixin.rb', line 82

def cd_all_id_table_tn
  "sr_#{self[:id]}_all_id_table"
end

#cd_id_table ⇒ Object

Entrega todos los id pertinentes para la revision sistematica



241
242
243
244
245
246
247
248
249
250
251
252
253
254
 
# File 'model/systematic_review_views_mixin.rb', line 241

def cd_id_table
  view_name = cd_id_table_tn
  if @cd_id_table_tn_exists.nil?
    @cd_id_table_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT DISTINCT(r.canonical_document_id) FROM records r INNER JOIN records_searches br ON r.id=br.record_id INNER JOIN searches b ON br.search_id=b.id WHERE b.systematic_review_id=#{self[:id]} AND b.valid=1

      UNION

      SELECT DISTINCT r.canonical_document_id FROM searches b INNER JOIN records_searches br ON b.id=br.search_id INNER JOIN records_references rr ON br.record_id=rr.record_id INNER JOIN bib_references r ON rr.reference_id=r.id  WHERE b.systematic_review_id=#{self[:id]} and r.canonical_document_id IS NOT NULL and b.valid=1 GROUP BY r.canonical_document_id")
    end
  end
  $db[view_name.to_sym]
end

#cd_id_table_tn ⇒ Object

Vistas especiales



236
237
238
 
# File 'model/systematic_review_views_mixin.rb', line 236

def cd_id_table_tn
  "sr_#{self[:id]}_cd_id"
end

#cd_record_id_table ⇒ Object 



38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
 
# File 'model/systematic_review_views_mixin.rb', line 38

def cd_record_id_table
  view_name = cd_record_id_table_tn
  if @cd_record_id_table_exists.nil?
    @cd_record_id_table_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS
SELECT cd.id as cd_id FROM canonical_documents cd INNER JOIN
records r ON cd.id=r.canonical_document_id INNER JOIN
records_searches rs ON r.id=rs.record_id INNER JOIN
searches s ON s.id=rs.search_id
where s.valid=1 AND s.systematic_review_id=#{self[:id]} GROUP BY cd.id")
    end
  end
  $db[view_name.to_sym]
end

#cd_record_id_table_tn ⇒ Object 



34
35
36
 
# File 'model/systematic_review_views_mixin.rb', line 34

def cd_record_id_table_tn
  "sr_#{self[:id]}_record_id_table"
end

#cd_reference_id_table ⇒ Object 



61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
 
# File 'model/systematic_review_views_mixin.rb', line 61

def cd_reference_id_table
  view_name = cd_reference_id_table_tn
  if @cd_reference_id_table_exists.nil?
    @cd_reference_id_table_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS
SELECT canonical_document_id as cd_id FROM searches b
INNER JOIN records_searches br ON b.id=br.search_id
INNER JOIN records_references rr ON br.record_id=rr.record_id
INNER JOIN bib_references r ON rr.reference_id=r.id
WHERE b.systematic_review_id=#{self[:id]} and r.canonical_document_id IS NOT NULL AND b.valid=1
GROUP BY r.canonical_document_id
")
    end
  end
  $db[view_name.to_sym]
end

#cd_reference_id_table_tn ⇒ Object 



57
58
59
 
# File 'model/systematic_review_views_mixin.rb', line 57

def cd_reference_id_table_tn
  "sr_#{self[:id]}_reference_id_table"
end

#count_references_bw_canonical ⇒ Object 



178
179
180
181
182
183
184
185
186
187
 
# File 'model/systematic_review_views_mixin.rb', line 178

def count_references_bw_canonical
  view_name = count_references_bw_canonical_tn
  if @count_references_bw_canonical_tn_exists.nil?
    @count_references_bw_canonical_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT cd.canonical_document_id as cd_id, COUNT(DISTINCT(r1.cd_end)) as n_total_references_made, COUNT(DISTINCT(r2.cd_start)) as n_total_references_in FROM #{cd_id_table_tn} cd LEFT JOIN #{references_bw_canonical_tn} r1 ON cd.canonical_document_id=r1.cd_start LEFT JOIN #{references_bw_canonical_tn} r2 ON cd.canonical_document_id=r2.cd_end GROUP BY cd.canonical_document_id")
    end
  end
  $db[view_name.to_sym]
end

#count_references_bw_canonical_tn ⇒ Object

THis is



191
192
193
 
# File 'model/systematic_review_views_mixin.rb', line 191

def count_references_bw_canonical_tn
  "sr_#{self[:id]}_references_between_cd_n"
end

#count_references_rtr ⇒ Object 



116
117
118
119
120
121
122
123
124
125
126
127
128
 
# File 'model/systematic_review_views_mixin.rb', line 116

def count_references_rtr
  references_bw_canonical
  resolutions_title_abstract # Verifico que exista la tabla de resolutions
  view_name = count_references_rtr_tn
  if @count_references_rtr_table_exists.nil?
      @count_references_rtr_table_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT cd_end , COUNT(DISTINCT(cd_start)) as n_references_rtr  FROM resolutions r INNER JOIN #{references_bw_canonical_tn} rec ON r.canonical_document_id=rec.cd_start LEFT JOIN #{resolutions_title_abstract_tn} as r2 ON r2.canonical_document_id=rec.cd_end WHERE r.systematic_review_id=#{self[:id]} and r.stage='screening_title_abstract' and r.resolution='yes' and r2.canonical_document_id IS NULL GROUP BY cd_end")
    end
  end
  $db[view_name.to_sym]

end

#count_references_rtr_tn ⇒ Object

Cuenta el número de references hechas a cada reference para la segunda stage Se eliminan como destinos aquellos documentos que ya fueron parte de la resolución de la primera stage



112
113
114
 
# File 'model/systematic_review_views_mixin.rb', line 112

def count_references_rtr_tn
  "sr_#{self[:id]}_references_between_cd_rtr_n"
end

#references_bw_canonical ⇒ Object

canonicos. Los campos son cd_start y cd_end



204
205
206
207
208
209
210
211
212
213
 
# File 'model/systematic_review_views_mixin.rb', line 204

def references_bw_canonical
  view_name = references_bw_canonical_tn
  if @references_bw_canonical_tn_exists.nil?
    @references_bw_canonical_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT r.canonical_document_id as cd_start, ref.canonical_document_id as cd_end FROM records r INNER JOIN records_searches br ON r.id=br.record_id INNER JOIN searches b ON br.search_id=b.id  INNER JOIN  records_references rr ON rr.record_id=r.id INNER JOIN bib_references ref ON ref.id=rr.reference_id   WHERE systematic_review_id='#{self[:id]}' AND ref.canonical_document_id IS NOT NULL AND b.valid=1 GROUP BY cd_start, cd_end")
    end
  end
  $db[view_name.to_sym]
end

#references_bw_canonical_tn ⇒ Object 



196
197
198
199
 
# File 'model/systematic_review_views_mixin.rb', line 196

def references_bw_canonical_tn
  "sr_#{self[:id]}_references_between_cd"

end

#resolutions_full_text ⇒ Object 



131
132
133
134
135
136
137
138
139
140
141
 
# File 'model/systematic_review_views_mixin.rb', line 131

def resolutions_full_text
  view_name = resolutions_full_text_tn
  if @resolutions_full_text_tn_exists.nil?
    @resolutions_full_text_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT * FROM resolutions  where systematic_review_id=#{self[:id]} and stage='review_full_text'")
    end

  end
  $db[view_name.to_sym]
end

#resolutions_full_text_tn ⇒ Object 



143
144
145
 
# File 'model/systematic_review_views_mixin.rb', line 143

def resolutions_full_text_tn
  "sr_#{self[:id]}_resolutions_full_text"
end

#resolutions_references ⇒ Object 



147
148
149
150
151
152
153
154
155
156
157
 
# File 'model/systematic_review_views_mixin.rb', line 147

def resolutions_references
  view_name = resolutions_references_tn
  if @resolutions_references_tn_exists.nil?
    @resolutions_references_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT * FROM resolutions  where systematic_review_id=#{self[:id]} and stage='screening_references'")
    end

  end
  $db[view_name.to_sym]
end

#resolutions_references_tn ⇒ Object 



159
160
161
 
# File 'model/systematic_review_views_mixin.rb', line 159

def resolutions_references_tn
  "sr_#{self[:id]}_resolutions_references"
end

#resolutions_title_abstract ⇒ Object 



163
164
165
166
167
168
169
170
171
172
 
# File 'model/systematic_review_views_mixin.rb', line 163

def resolutions_title_abstract
  view_name = resolutions_title_abstract_tn
  if @resolutions_title_abstract_tn_exists.nil?
    @resolutions_title_abstract_tn_exists=true
    if !$db.table_exists?(view_name)
      $db.run("CREATE VIEW #{view_name} AS SELECT * FROM resolutions  where systematic_review_id=#{self[:id]} and stage='screening_title_abstract'")
    end
  end
  $db[view_name.to_sym]
end

#resolutions_title_abstract_tn ⇒ Object 



174
175
176
 
# File 'model/systematic_review_views_mixin.rb', line 174

def resolutions_title_abstract_tn
  "sr_#{self[:id]}_resolutions_sta"
end