/Users/smr/current_projects/pws2016-work/pull-grs1-users/
pull-grs1-users:
get-grs1-data.rb
grs-non_school_users.yaml
grs-schools.yaml
notes.txt
users-activations-UNUSED.json
##################################################
# first attempt
##################################################
select u.*, a.actcode, a.activationTimestamp, a.type, a.activator_id from user u
join activation a on u.id = a.activator_id
where login != 'login'
this includes all users
##################################################
# non-school enrollments
##################################################
914 enrollments
565 not expired
single user grs enrollments not associated with a school
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type, u.email, u.firstname, u.lastname, u.password from enrollment e
join user u on u.id = e.student_id
join activation a on a.id = e.activation_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot' and e.`school_id` = 0 and a.type = 'su'
##################################################
# instructors
##################################################
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type as activation_type, u.email, u.firstname, u.lastname, u.password, u.role, s.name from enrollment e
join user u on u.id = e.`instructor_id`
join activation a on a.id = e.activation_id
join school s on s.id = e.school_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot'
##################################################
# students
##################################################
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type as activation_type, u.email, u.firstname, u.lastname, u.password, u.role, s.name from enrollment e
join user u on u.id = e.`student_id`
join activation a on a.id = e.activation_id
join school s on s.id = e.school_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot'
##################################################
# another idea
##################################################
might be better to write a ruby script to mine the data, and create one big data
structure that in can import, to include all users, schools, and instructor-student relationships
see /Users/smr/current_projects/pws2016-work/pull-grs1-users/get-grs1-data.rb
pull-grs1-users:
get-grs1-data.rb
grs-non_school_users.yaml
grs-schools.yaml
notes.txt
users-activations-UNUSED.json
##################################################
# first attempt
##################################################
select u.*, a.actcode, a.activationTimestamp, a.type, a.activator_id from user u
join activation a on u.id = a.activator_id
where login != 'login'
this includes all users
##################################################
# non-school enrollments
##################################################
914 enrollments
565 not expired
single user grs enrollments not associated with a school
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type, u.email, u.firstname, u.lastname, u.password from enrollment e
join user u on u.id = e.student_id
join activation a on a.id = e.activation_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot' and e.`school_id` = 0 and a.type = 'su'
##################################################
# instructors
##################################################
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type as activation_type, u.email, u.firstname, u.lastname, u.password, u.role, s.name from enrollment e
join user u on u.id = e.`instructor_id`
join activation a on a.id = e.activation_id
join school s on s.id = e.school_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot'
##################################################
# students
##################################################
select e.`productname`, e.`completed`, e.`completion_date`, e.`expiry_date`, a.actcode, a.activationTimestamp, a.type as activation_type, u.email, u.firstname, u.lastname, u.password, u.role, s.name from enrollment e
join user u on u.id = e.`student_id`
join activation a on a.id = e.activation_id
join school s on s.id = e.school_id
where e.`expiry_date` > now() and e.`productname` = 'Private Pilot'
##################################################
# another idea
##################################################
might be better to write a ruby script to mine the data, and create one big data
structure that in can import, to include all users, schools, and instructor-student relationships
see /Users/smr/current_projects/pws2016-work/pull-grs1-users/get-grs1-data.rb
