#!/bin/bash
#
# millering_vaers 0.1
#
# Miller is hiding nonsense behind undisclosed data
# http://www.jpands.org/vol21no2/miller.pdf
#
# You too can be a "scientist" and do the same "study" Miller did 
#
#
# Download VAERS data, (https://vaers.hhs.gov/data)
# Launch the script
# Wait two minutes 
# See the data Miller kept for himself
#
# Voilà, you are a "scientist"
#
#

if [[ "$2" == "" ]]; then 

  echo "Add two years and start millering VAERS"
  echo "  "$0" 1995 2015"
  exit 1

fi

FROMYEAR="$1"
TOYEAR="$2"

echo -e "\n\nMillering VAERS files from $FROMYEAR through $TOYEAR\n\n"

echo 'Miller: "We started by downloading the complete VAERS database from 1990 through 2010"'
echo -e "We started by downloading the complete VAERS database from $FROMYEAR through $TOYEAR"

stop=0
for i in $(seq $FROMYEAR $TOYEAR); do

  for j in VAERSDATA VAERSVAX VAERSSYMPTOMS; do
    if [[ ! -e $i$j".csv" ]]; then
      echo -en "\n"$i$j".csv: Missing !"
      stop=1
    fi
  done

done

if [[ "$stop" == "1" ]]; then
  echo -e "\n\nFiles are missing. Have a look here: https://vaers.hhs.gov/data/data\n\n"
  exit 1
fi

echo "Creating and importing ... (Please wait)"

rm vaers.db 2> /dev/null

echo "
  create table VAERSDATA(VAERS_ID integer primary key asc,RECVDATE,STATE,AGE_YRS numeric,CAGE_YR numeric,CAGE_MO numeric,SEX,RPT_DATE,SYMPTOM_TEXT,DIED,DATEDIED,L_THREAT,ER_VISIT,HOSPITAL,HOSPDAYS numeric,X_STAY,DISABLE,RECOVD,VAX_DATE,ONSET_DATE,NUMDAYS numeric,LAB_DATA,V_ADMINBY,V_FUNDBY,OTHER_MEDS,CUR_ILL,HISTORY,PRIOR_VAX,SPLTTYPE);

  create table VAERSVAX(VAERS_ID integer,VAX_TYPE,VAX_MANU,VAX_LOT,VAX_DOSE,VAX_ROUTE,VAX_SITE,VAX_NAME,FOREIGN KEY(VAERS_ID) REFERENCES VAERSDATA(VAERS_ID));

  create table VAERSSYMPTOMS(VAERS_ID integer,SYMPTOM1,SYMPTOMVERSION1 numeric,SYMPTOM2,SYMPTOMVERSION2 numeric,SYMPTOM3,SYMPTOMVERSION3 numeric,SYMPTOM4,SYMPTOMVERSION4 numeric,SYMPTOM5,SYMPTOMVERSION5 numeric,FOREIGN KEY(VAERS_ID) REFERENCES VAERSDATA(VAERS_ID));

" | sqlite3 vaers.db -separator ','


for i in $(seq $FROMYEAR $TOYEAR); do

  echo -n '.' 
  echo ".import "$i"VAERSDATA.csv VAERSDATA" | sqlite3 -separator ',' vaers.db
  echo ".import "$i"VAERSVAX.csv VAERSVAX" | sqlite3 -separator ',' vaers.db
  echo ".import "$i"VAERSSYMPTOMS.csv VAERSSYMPTOMS" | sqlite3 -separator ',' vaers.db

done

echo " "
echo 'Miller: "There were more than 325,000 VAERS"'
echo -e "There were $( sqlite3 vaers.db 'select count (*) from VAERSDATA;'  ) VAERS\n"




echo 'Miller: "We then eliminated all case reports that were not associated with infants (babies aged up to one year). This left us with 38,801 VAERS"'
echo -e "We then eliminated all case reports that were not associated with infants (babies aged up to one year). This left us with $( sqlite3 vaers.db 'select count(*) from VAERSDATA d where AGE_YRS < 1;' ) VAERS\n"


echo 'Miller: "Next, we determined how many vaccine doses each infant received prior to the adverse event."'
echo -e "Ditto

Dosing ... (Please Wait)\n"

echo "

  alter table VAERSVAX add column VAX_TYPE_coef numeric;

  update VAERSVAX set VAX_TYPE_coef = 1;
  update VAERSVAX set VAX_TYPE_coef = 2 where VAX_TYPE in ('DT','HBHEPB','HEPAB','HEPATYP','MER','MM','MNQHIB','MUR','TD');
  update VAERSVAX set VAX_TYPE_coef = 3 where VAX_TYPE in ('DTP','MMR','DPP','DTAP','DTIPV','TDAP');
  update VAERSVAX set VAX_TYPE_coef = 4 where VAX_TYPE in ('DTAPH','DTAPIPV','DTPHEP','DTPHIB','DTPIPV','DTaPIPV','MMRV','TDAPIPV');
  update VAERSVAX set VAX_TYPE_coef = 5 where VAX_TYPE in ('DTAPHEPBIP','DTAPIPVHIB','DTPIHI','DTPPHIB','DTaPIPVHib');
  update VAERSVAX set VAX_TYPE_coef = 6 where VAX_TYPE in ('6VAX-F');

" | sqlite3 -separator ',' vaers.db


echo 'Miller: "Finally, we isolated the “serious” adverse"'
echo -e "Ditto

Seriousing ... (Please Wait)
\n"


echo "

  alter table VAERSDATA add column serious bool;

  update VAERSDATA set serious = 0;
  update VAERSDATA set serious = 1 where ( DIED = 'Y' OR L_THREAT = 'Y' OR HOSPITAL = 'Y' OR X_STAY = 'Y' OR DISABLE = 'Y' );

" | sqlite3 -separator ',' vaers.db


echo "
1990 - 2010 (Miller):
coef        vaers       serious     serious_pct  hospital    hospital_pct  died        died_pct  
----------  ----------  ----------  -----------  ----------  ------------  ----------  ----------
1           5038        1083        21.5 %       812         16.12 %       196         3.89 %    
2           956         139         14.54 %      104         10.88 %       20          2.09 %    
3           1941        370         19.06 %      242         12.47 %       41          2.11 %    
4           3884        792         20.39 %      555         14.29 %       163         4.2 %     
5           10057       2079        20.67 %      1449        14.41 %       522         5.19 %    
6           8356        1903        22.77 %      1340        16.04 %       490         5.86 %    
7           5427        1409        25.96 %      1032        19.02 %       321         5.91 %    
8           2812        798         28.38 %      658         23.4 %        127         4.52 %    
9           205         30          14.63 %      24          11.71 %       2           0.98 %    
10          42          8           19.05 %      4           9.52 %        1           2.38 %    
11          25          2           8.0 %        2           8.0 %         0           0.0 %     
12          13          3           23.08 %      3           23.08 %       0           0.0 %     
13          3           0           0.0 %        0           0.0 %         0           0.0 %     
"


echo "
Millering ... (please wait)

$FROMYEAR - $TOYEAR:"
echo "

.header on
.mode column

select 
  sum_coef coef, sum(vaers) vaers, 
  sum(serious) serious, round( sum(serious) * 100.0 / sum(vaers) , 2 ) || ' %' serious_pct,
  sum(hospital) hospital, round( sum(hospital) * 100.0 / sum(vaers) , 2 ) || ' %' hospital_pct,
  sum(died) died, round( sum(died) * 100.0 / sum(vaers) , 2 ) || ' %' died_pct
from

  ( select SUM(VAX_TYPE_coef) sum_coef, 1 vaers, serious, (HOSPITAL = 'Y') hospital, (DIED = 'Y') died    
    from VAERSVAX v, VAERSDATA d
    where 
      d.VAERS_ID = v.VAERS_ID and 
      AGE_YRS < 1
    group by v.VAERS_ID     
  ) vax

group by sum_coef;

" | sqlite3 -separator ',' vaers.db

echo -e "\n\nMillering Done."