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 



137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
 
# File 'model/systematic_review_views_mixin.rb', line 137

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 



154
155
156
 
# File 'model/systematic_review_views_mixin.rb', line 154

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

#cd_id_table ⇒ Object

Entrega todos los id pertinentes para la revision sistematica



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

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



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

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

#count_references_bw_canonical ⇒ Object 



100
101
102
103
104
105
106
107
108
109
 
# File 'model/systematic_review_views_mixin.rb', line 100

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



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

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

#count_references_rtr ⇒ Object 



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

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

Se eliminan como destinos aquellos documentos que ya fueron parte de la resolución de la primera stage



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

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



126
127
128
129
130
131
132
133
134
135
 
# File 'model/systematic_review_views_mixin.rb', line 126

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 



118
119
120
121
 
# File 'model/systematic_review_views_mixin.rb', line 118

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

end

#resolutions_full_text ⇒ Object 



53
54
55
56
57
58
59
60
61
62
63
 
# File 'model/systematic_review_views_mixin.rb', line 53

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 



65
66
67
 
# File 'model/systematic_review_views_mixin.rb', line 65

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

#resolutions_references ⇒ Object 



69
70
71
72
73
74
75
76
77
78
79
 
# File 'model/systematic_review_views_mixin.rb', line 69

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 



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

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

#resolutions_title_abstract ⇒ Object 



85
86
87
88
89
90
91
92
93
94
 
# File 'model/systematic_review_views_mixin.rb', line 85

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 



96
97
98
 
# File 'model/systematic_review_views_mixin.rb', line 96

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