-
Notifications
You must be signed in to change notification settings - Fork 10
QUERI Scripts
Below is a list of scripts written for the QUERI team to help them analyze Diffusion Marketplace's data
select dh.va_facility_id, dh.practice_id, p.name, vaf.street_address_state, vaf.official_station_name, vaf.visn_id as visn, vaf.sta3n, vaf.station_number, dh.created_at as adoption_date, dhs.status, vaf.rurality, vaf.fy17_parent_station_complexity_level from diffusion_histories dh JOIN va_facilities vaf on dh.va_facility_id = vaf.id JOIN practices p on dh.practice_id = p.id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id
"sudo docker exec diffusion-marketplace_app_1 rails runner \"ActiveRecord::Base.logger = nil; User.all.order(Arel.sql(%Q(lower(location) ASC))).each { |u| puts %Q(User ID: #{u.id} - User Location: #{u.location.present? ? u.location : 'N/A'}) }; nil; STDOUT.flush;\";"
Total number of innovation page views: puts %Q(Total Innovation Views: #{Practice.published.collect { |p| p.views }.sum});
Total number of published innovations: puts %Q(Total Innovations Published: #{Practice.where(published: true).size});
Total number of user accounts: puts %Q(Total User Accounts: #{User.all.size});
Total number of comments created: puts %Q(Total Comments: #{Commontator::Comment.all.size});
Total number of innovations bookmarked: puts %Q(Total Number of Innovations Bookmarked: #{UserPractice.where(favorited: true).size});
**Total Number of Innovation Views for Previous Month: Jan 2022 (result: 2158)**
puts %Q(Total Innovation Views for previous month: #{Practice.published.collect { |p| p.date_range_views((Time.now.beginning_of_month - 1.month).beginning_of_day, Time.now.beginning_of_month.beginning_of_day) }.sum});
** Total innovation views for “Veterans Coordinated Approach to Recovery and Employment” for Jan 2022 – (result: 147) **
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner \"ActiveRecord::Base.logger = nil; puts %Q(Total Innovation Views for previous month: #{Practice.where(published: 'true', name: 'Veterans Coordinated Approach to Recovery and Employment').collect { |p| p.date_range_views((Time.now.beginning_of_month - 1.month).beginning_of_day, Time.now.beginning_of_month.beginning_of_day)}.sum});\";"
**Total innovation views for “Veterans Coordinated Approach to Recovery and Employment” for Dec 2021 – (result: 10) **
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner \"ActiveRecord::Base.logger = nil; puts %Q(Total Innovation Views for previous month: #{Practice.where(published: 'true', name: 'Veterans Coordinated Approach to Recovery and Employment').collect { |p| p.date_range_views((Time.now.beginning_of_month - 2.month).beginning_of_day, (Time.now.beginning_of_month - 1.month).beginning_of_day)}.sum});\";"
NOTE: replace [year], [month] values appropriately
year = [year]; month = [month]; start_time = DateTime.new(year, month).beginning_of_month.beginning_of_day; end_time = DateTime.new(year, month).end_of_month.end_of_day; count = Ahoy::Event.where(name: 'Site visit').where(%Q(properties->>'ip_address' is not null)).where(%Q(properties->>'is_duplicate' is null)).where(time: start_time..end_time).count; puts %Q(Site views count for #{start_time} - #{end_time}: #{count});
NOTE: replace [year], [month], [slug] values appropriately
year = [year]; month = [month]; slug = [slug]; start_time = DateTime.new(year, month).beginning_of_month.beginning_of_day; end_time = DateTime.new(year, month).end_of_month.end_of_day; views_count = Practice.where(published: 'true', slug: slug).collect { |p| p.date_range_views(start_time, end_time)}.sum; puts %Q(#{slug} views count for #{start_time} - #{end_time}: #{views_count})
NOTE: replace [start_year], [start_month], [start_day], [end_year], [end_month], and [end_day] values appropriately
start_year = [start_year]; start_month = [start_month]; start_day = [start_day]; end_year = [end_year]; end_month = [end_month]; end_day = [end_day]; start_time = DateTime.new(start_year, start_month, start_day).beginning_of_day; end_time = DateTime.new(end_year, end_month, end_day).end_of_day; site_visits = Ahoy::Event.where(name: 'Site visit').where(%Q(properties->>'ip_address' is not null)).where(%Q(properties->>'is_duplicate' is null)).where(time: start_time..end_time).size; puts %Q(Total site views for #{start_time.to_formatted_s(:long_ordinal)} - #{end_time.to_formatted_s(:long_ordinal)}: #{site_visits})
NOTE: replace [year], [month], and [day] values appropriately
year = [year]; month = [month]; day = [day]; start_time = DateTime.new(year, month, day).beginning_of_day; site_visits = Ahoy::Event.where(name: 'Site visit').where(%Q(properties->>'ip_address' is not null)).where(%Q(properties->>'is_duplicate' is null)).where(time: start_time..DateTime.now()).size; puts %Q(Total site views for #{start_time.to_formatted_s(:long_ordinal)} - present: #{site_visits})
NOTE: replace [year], [month], and [day] values appropriately
year = [year]; month = [month]; day = [day]; start_time = DateTime.new(year, month, day).beginning_of_day; site_visits = Ahoy::Event.where(name: 'Site visit').where(%Q(properties->>'ip_address' is not null)).where(%Q(properties->>'is_duplicate' is null)).where(%Q(properties->>'page_group' = 'competitions')).where(%Q(properties->>'page_slug' = 'shark-tank')).where(time: start_time..DateTime.now()).size; puts %Q(Shark Tank page views for #{start_time.to_formatted_s(:long_ordinal)} - present: #{site_visits})
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner \"ActiveRecord::Base.logger = nil; Practice.where(published: true).order(name: :asc).each { |p| puts %Q(Name: #{p.name}, Published: #{p.date_published}, Updated: #{p.updated_at})}; nil; STDOUT.flush\";"
Total number of published innovations since February 2020 Note: Not putting date range since we only started the publish date field in Dec 7, 2020
Practice.where(published: true).count
Total number of user accounts since February 2020
year = 2020; month = 2; day = 1; start_time = DateTime.new(year, month, day).beginning_of_day; User.where(created_at: start_time..DateTime.now()).count
Total number of Innovation Page Views since February 2020
year = 2020; month = 2; day = 1; start_time = DateTime.new(year, month, day).beginning_of_day; Practice.published.collect { |p| p.date_range_views(start_time, DateTime.now()) }.sum
Total number of Community Comments since February 2020
year = 2020; month = 2; day = 1; start_time = DateTime.new(year, month, day).beginning_of_day; Commontator::Comment.where(created_at: start_time..DateTime.now()).count
Total number of Innovation Bookmarks since February 2020
year = 2020; month = 2; day = 1; start_time = DateTime.new(year, month, day).beginning_of_day; UserPractice.where(time_favorited: start_time..DateTime.now()).count
Adoptions for QUERI folks.
DiffusionHistory.joins(:va_facility, :practice).order(Arel.sql(%Q(lower(practices.name)))).collect { |dh| adoption_date = dh.diffusion_history_statuses.first.status == 'In progress' || dh.diffusion_history_statuses.first.status == 'Implementing' || dh.diffusion_history_statuses.first.status == 'Planning' || dh.diffusion_history_statuses.first.status == 'Unsuccessful' ? dh.diffusion_history_statuses.first.start_time : dh.diffusion_history_statuses.first.end_time; { practice: dh.practice.name, state: dh.va_facility.mailing_address_state, location: dh.va_facility.official_station_name, originating_facilities: origin_display_name(dh.practice), visn: dh.va_facility.visn.number, sta3n: dh.va_facility.sta3n, station_number: dh.va_facility.station_number, adoption_date: adoption_date.blank? ? 'N/A' : adoption_date.strftime('%B %Y'), adoption_status: dh.diffusion_history_statuses.first.status == 'Completed' || dh.diffusion_history_statuses.first.status == 'Implemented' || dh.diffusion_history_statuses.first.status == 'Complete' ? 'Successful' : dh.diffusion_history_statuses.first.status, rurality: dh.va_facility.rurality == 'U' ? 'Urban' : 'Rural', facility_complexity: dh.va_facility.fy17_parent_station_complexity_level } }.to_json
//////////////////////////////////////////////////////////////////////////
RAW SQL Execute examples - QUERI Adoption examples
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner "ActiveRecord::Base.logger = nil; sql = 'select id from practices'; records_array = ActiveRecord::Base.connection.execute(sql).to_a; puts %Q(#{records_array})"";
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner "ActiveRecord::Base.logger = nil; sql = 'select vaf.id, vaf.official_station_name as location, vaf.station_number, vaf.sta3n, dhs.status, p.id as practice_id, p.name as practice_name, crh.id, crh.official_station_name from diffusion_histories dh JOIN practices p on p.id = dh.practice_id JOIN va_facilities vaf on vaf.id = dh.va_facility_id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id LEFT JOIN clinical_resource_hubs crh on crh.id = dh.clinical_resource_hub_id UNION select crh.id, crh.official_station_name as crh_location, NULL, NULL, dhs.status, p.id as practice_id, p.name as practice_name, crh.id, crh.official_station_name from diffusion_histories dh JOIN practices p on p.id = dh.practice_id JOIN clinical_resource_hubs crh on crh.id = dh.clinical_resource_hub_id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id';
records_array = ActiveRecord::Base.connection.execute(sql).to_a; puts %Q(#{records_array})"";
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner "ActiveRecord::Base.logger = nil; sql = 'select p.name as practice_name, vaf.id, vaf.official_station_name as location, vaf.station_number, vaf.sta3n, dhs.status, p.id as practice_id, crh.id, crh.official_station_name from diffusion_histories dh JOIN practices p on p.id = dh.practice_id JOIN va_facilities vaf on vaf.id = dh.va_facility_id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id LEFT JOIN clinical_resource_hubs crh on crh.id = dh.clinical_resource_hub_id UNION select p.name as practice_name, crh.id, crh.official_station_name as crh_location, NULL, NULL, dhs.status, p.id as practice_id, crh.id, crh.official_station_name from diffusion_histories dh JOIN practices p on p.id = dh.practice_id JOIN clinical_resource_hubs crh on crh.id = dh.clinical_resource_hub_id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id';
records_array = ActiveRecord::Base.connection.execute(sql).to_a; puts %Q(#{records_array})"";
With no (CRHs) and no Originating Facilities UNION
ssh ec2-user@$PROD_SERVER "sudo docker exec diffusion-marketplace_app_1 rails runner "ActiveRecord::Base.logger = nil; sql = 'select vaf.id as facilityId, p.id as practiceId, p.name as Practice, vaf.street_address_state as state, vaf.official_station_name as location, vn.number as VISN, vaf.sta3n, vaf.station_number, dh.created_at as Adoption_Date, dhs.status, vaf.rurality, vaf.fy17_parent_station_complexity_level from diffusion_histories dh JOIN practices p on p.id = dh.practice_id JOIN va_facilities vaf on vaf.id = dh.va_facility_id JOIN diffusion_history_statuses dhs on dh.id = dhs.diffusion_history_id JOIN visns vn on vn.id = vaf.visn_id';
records_array = ActiveRecord::Base.connection.execute(sql).to_json; puts %Q(#{records_array})"";
//////////////////////////////////////////////////////////////////////