changed symlinks

14 files changed, 154 insertions, 1 deletions

diff --git a/queries b/queries
deleted file mode 120000
index 34efd61..0000000
--- a/queries
+++ /dev/null
@@ -1 +0,0 @@
-/home/mike/.local/share/db_ui/fluprint/
\ No newline at end of file
diff --git a/queries/data_per_assay_per_year_per_outcome b/queries/data_per_assay_per_year_per_outcome
new file mode 100644
index 0000000..2f8c063
--- /dev/null
+++ b/queries/data_per_assay_per_year_per_outcome
@@ -0,0 +1,17 @@
+SELECT donors.study_id                  AS study,
+       donors.id                        as donor,
+       donor_visits.visit_year          AS year,
+       donor_visits.vaccine_resp        AS outcome,
+       experimental_data.assay          AS assay,
+       experimental_data.data           AS data,
+       experimental_data.name_formatted as name,
+       experimental_data.units
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+       INNER JOIN experimental_data
+               ON donor_visits.id = experimental_data.donor_visits_id
+                  AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine_resp IS NOT NULL
+ORDER BY donor_visits.visit_year ASC
diff --git a/queries/donor_demo b/queries/donor_demo
new file mode 100644
index 0000000..dd5077a
--- /dev/null
+++ b/queries/donor_demo
@@ -0,0 +1,11 @@
+SELECT  donors.id as Donor,
+        donors.gender as Gender,
+        donor_visits.age_round as Age,
+        donors.race as Ethnicity,
+        donor_visits.cmv_status as 'CMV status',
+        donor_visits.vaccine_resp as Response
+from donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+                 AND donor_visits.visit_id = 1
+where donor_visits.vaccine_resp is not null
diff --git a/queries/donors_list b/queries/donors_list
new file mode 100644
index 0000000..e3610bd
--- /dev/null
+++ b/queries/donors_list
@@ -0,0 +1 @@
+SELECT * from `donors`
diff --git a/queries/donors_outcome_across_years b/queries/donors_outcome_across_years
new file mode 100644
index 0000000..1cbf55f
--- /dev/null
+++ b/queries/donors_outcome_across_years
@@ -0,0 +1,18 @@
+SELECT donors.id                        AS donor_id,
+       donor_visits.age                 AS age,
+       donor_visits.vaccine_resp        AS outcome,
+       donor_visits.visit_id            AS visit,
+       donor_visits.visit_day           AS day,
+       donor_visits.visit_year          AS year
+       # experimental_data.name_formatted AS data_name,
+       # experimental_data.data           AS data,
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+                 # AND donor_visits.visit_id = 1
+       # INNER JOIN experimental_data
+       #         ON donor_visits.id = experimental_data.donor_visits_id
+       #            AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine = 4
+ORDER  BY donors.id DESC
diff --git a/queries/high_reponders b/queries/high_reponders
new file mode 100644
index 0000000..3f33373
--- /dev/null
+++ b/queries/high_reponders
@@ -0,0 +1,4 @@
+SELECT * from donor_visits
+where vaccine_resp is not null
+and donor_id = 166
+order by visit_year ASC
diff --git a/queries/mike_repeat_visit b/queries/mike_repeat_visit
new file mode 100644
index 0000000..c0bfbfd
--- /dev/null
+++ b/queries/mike_repeat_visit
@@ -0,0 +1,23 @@
+SELECT donors.id                        AS donor_id,
+       donors.study_id                  AS study,
+       donor_visits.age                 AS age,
+       donor_visits.vaccine_resp        AS outcome,
+       donor_visits.visit_year          AS year,
+       donor_visits.visit_type_hai      as type,
+       donor_visits.d_geo_mean          as hai_response,
+       experimental_data.name           AS name,
+       experimental_data.name_formatted AS name_formatted,
+       experimental_data.assay          AS assay,
+       experimental_data.data           AS data
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+       INNER JOIN experimental_data
+               ON donor_visits.id = experimental_data.donor_visits_id
+                  AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine_resp IS NOT NULL
+       AND donor_visits.vaccine = 4
+       AND donor_visits.visit_type_hai = "pre"
+       AND donor_visits.visit_id != 1
+ORDER  BY donors.study_donor_id DESC
diff --git a/queries/null_donors b/queries/null_donors
new file mode 100644
index 0000000..4646677
--- /dev/null
+++ b/queries/null_donors
@@ -0,0 +1,7 @@
+SELECT * from donors
+where donors.id is NULL or
+donors.study_donor_id is NULL or
+donors.study_id is NULL or
+donors.study_internal_id is NULL or
+donors.gender is NULL or
+donors.race is NULL
diff --git a/queries/repeat_investigation b/queries/repeat_investigation
new file mode 100644
index 0000000..293e21c
--- /dev/null
+++ b/queries/repeat_investigation
@@ -0,0 +1,5 @@
+SELECT *
+from `donor_visits`
+left join donors on
+    donors.id = donor_visits.donor_id
+where study_id = 15
diff --git a/queries/simon_data_extra b/queries/simon_data_extra
new file mode 100644
index 0000000..ea97abb
--- /dev/null
+++ b/queries/simon_data_extra
@@ -0,0 +1,22 @@
+SELECT donors.id                        AS donor_id,
+       donors.study_id                  AS study,
+       donor_visits.age                 AS age,
+       donor_visits.vaccine_resp        AS outcome,
+       donor_visits.visit_year          AS year,
+       donor_visits.visit_type_hai      as type,
+       donor_visits.d_geo_mean          as hai_response,
+       experimental_data.name           AS name,
+       experimental_data.name_formatted AS data_name,
+       experimental_data.assay          AS assay,
+       experimental_data.data           AS data
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+                 AND donor_visits.visit_id = 1
+       INNER JOIN experimental_data
+               ON donor_visits.id = experimental_data.donor_visits_id
+                  AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine_resp IS NOT NULL
+       AND donor_visits.vaccine = 4
+ORDER  BY donors.study_donor_id DESC
diff --git a/queries/simon_initial b/queries/simon_initial
new file mode 100644
index 0000000..f7ae18a
--- /dev/null
+++ b/queries/simon_initial
@@ -0,0 +1,16 @@
+SELECT donors.id                        AS donor_id,
+       donor_visits.age                 AS age,
+       donor_visits.vaccine_resp        AS outcome,
+       experimental_data.name_formatted AS data_name,
+       experimental_data.data           AS data
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+                 AND donor_visits.visit_id = 1
+       INNER JOIN experimental_data
+               ON donor_visits.id = experimental_data.donor_visits_id
+                  AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine_resp IS NOT NULL
+       AND donor_visits.vaccine = 4
+ORDER  BY donors.study_donor_id DESC
diff --git a/queries/simon_initial_check b/queries/simon_initial_check
new file mode 100644
index 0000000..703a685
--- /dev/null
+++ b/queries/simon_initial_check
@@ -0,0 +1,16 @@
+SELECT donors.id                        AS donor_id,
+       donor_visits.age                 AS age,
+       donor_visits.vaccine_resp        AS outcome,
+       # experimental_data.name_formatted AS data_name,
+       # experimental_data.data           AS data,
+       donor_visits.visit_year          AS year
+FROM   donors
+       LEFT JOIN donor_visits
+              ON donors.id = donor_visits.donor_id
+                 # AND donor_visits.visit_id = 1
+       # INNER JOIN experimental_data
+       #         ON donor_visits.id = experimental_data.donor_visits_id
+       #            AND experimental_data.donor_id = donor_visits.donor_id
+WHERE  donors.gender IS NOT NULL
+       AND donor_visits.vaccine = 4
+ORDER  BY donors.id DESC
diff --git a/queries/visit_multiple_21 b/queries/visit_multiple_21
new file mode 100644
index 0000000..d8a16fa
--- /dev/null
+++ b/queries/visit_multiple_21
@@ -0,0 +1,11 @@
+SELECT * from `donor_visits`
+join(
+select donor_visits.donor_id
+from donor_visits
+group by donor_visits.donor_id
+having count(*) > 1) s on donor_visits.donor_id = s.donor_id
+join donors
+on donors.id = donor_visits.donor_id
+where study_id = 21
+and donor_visits.donor_id = 166
+order by donor_visits.donor_id, visit_id
diff --git a/queries/visits_all b/queries/visits_all
new file mode 100644
index 0000000..0a6dcfa
--- /dev/null
+++ b/queries/visits_all
@@ -0,0 +1,3 @@
+SELECT * from `donor_visits`
+where donor_id = 779
+order by visit_year