IL CTE Gloabal Change Needed

[dpentony]

We need to give all IL CTE subscribers access to the Graphic Communications lesson plans, Eunits, and CSAT. Activate/Deactivates dates should be the same as all other libraries. Can you do it globally?

[dpentony]

We should also probably take a look at all the CTE libraries, for some reason many of them will expire on 12/1/2014, others expire 7/1/2015. Can we make the change at the same time on this? All CTE users should have expiration dates of 7/1/2015.

[bguerrettaz]

Ok, I'm doing some initial analysis on this issue, want to make sure we get it right holistically.

• Should all IL, CTE teachers be subscribed to the EXACT same thing (Lesson Plans, EUnits, and CSAT)?
• Should they all have the same dates?

If both of these are true, then I would like to make all IL, CTE teachers synced up. If not, then let me know and I'll leave as is.

FYI, there are 7705 registered, active, IL, CTE teachers in the system.

[dpentony]

Yes, all Illinois CTE teachers should have access to exactly the same materials and they should all have the same dates. Remember this is CTE teachers and not those classified as Ag.

[bguerrettaz]

Yes, I had filtered only active, CTE teachers in IL.

Ok, if you're on board, I'll go ahead and work to sync all of those teachers up.

On Mon, Sep 29, 2014 at 8:00 AM, dpentony notifications@github.com wrote:

Yes, all Illinois CTE teachers should have access to exactly the same materials and they should all have the same dates. Remember this is CTE teachers and not those classified as Ag.

— Reply to this email directly or view it on GitHub https://github.com/MyCaert/Issues-Enhancements/issues/88#issuecomment-57156579 .

[bguerrettaz]

Here is what all IL, CTE, Active teachers (7705 is the current count of these teachers) will have access to:

Technology & Engineering Education Health Science Technology Business, Marketing, and Computer Education Introduction to FCS Careers Drafting & Design Human Growth & Development Medical Assistant Manufacturing and Production Video Game Programming Pharmacy Technician Foods, Nutrition, and Culinary Arts Green and Energy-Efficient Construction Textiles and Design School Career Counseling Paralegal Services Career Development Emergency Medical Services Consumer Education Entrepreneurship

PLUS, I'll add Graphic Communications.

The expiration dates the Lesson Plans, EUnits, CSATs associated with these teachers and lessons will be 7/1/2015.

Agree with all of this?

[dpentony]

Yes. Looks good.

[bguerrettaz]

Ok, sorry this took so long, but there was a lot of query writing and I wanted to make sure it was all correct. Please test some users and let me know. I'm including the queries/inserts below for reference.

Hours 6.5

select * from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active' order by teacherid

--Update all IL, CTE, Active teachers to have the same lesson access update tblTeacher set LessonPlanArray = '26, 27, 28, 29, 30, 31, 34, 35, 37, 41, 51, 54, 56, 57, 58, 68, 69, 81, 82, 83', eUnitArray = '26, 27, 28, 29, 30, 31, 34, 35, 37, 41, 51, 54, 56, 57, 58, 68, 69, 81, 82, 83', CSATArray = '26, 27, 28, 29, 30, 31, 34, 35, 37, 41, 51, 54, 56, 57, 58, 68, 69, 81, 82, 83' where TeacherID IN ( select teacherid from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active' )

--Backup tblLessonPlanActivationDates select * into tblLessonPlanActivationDates_100114 from tblLessonPlanActivationDates

--Delete old records to prepare for new records delete from tblLessonPlanActivationDates where TeacherID IN ( select teacherid from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active' )

--26 insert into tblLessonPlanActivationDates select teacherid, 26 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --27 insert into tblLessonPlanActivationDates select teacherid, 27 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --28 insert into tblLessonPlanActivationDates select teacherid, 28 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --29 insert into tblLessonPlanActivationDates select teacherid, 29 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --30 insert into tblLessonPlanActivationDates select teacherid, 30 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --31 insert into tblLessonPlanActivationDates select teacherid, 31 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --34 insert into tblLessonPlanActivationDates select teacherid, 34 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --35 insert into tblLessonPlanActivationDates select teacherid, 35 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --37 insert into tblLessonPlanActivationDates select teacherid, 37 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --41 insert into tblLessonPlanActivationDates select teacherid, 41 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --51 insert into tblLessonPlanActivationDates select teacherid, 51 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --54 insert into tblLessonPlanActivationDates select teacherid, 54 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --56 insert into tblLessonPlanActivationDates select teacherid, 56 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --57 insert into tblLessonPlanActivationDates select teacherid, 57 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --58 insert into tblLessonPlanActivationDates select teacherid, 58 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --68 insert into tblLessonPlanActivationDates select teacherid, 68 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --69 insert into tblLessonPlanActivationDates select teacherid, 69 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --81 insert into tblLessonPlanActivationDates select teacherid, 81 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --82 insert into tblLessonPlanActivationDates select teacherid, 82 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --83 insert into tblLessonPlanActivationDates select teacherid, 83 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate, 'IL' as State from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active';

--Backup tblEunitActivationDates table select * into tblEunitActivationDates_100114 from tblEunitActivationDates

--Delete old records to prepare for new records delete from tblEunitActivationDates where TeacherID IN ( select teacherid from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active' )

--26 insert into tblEunitActivationDates select 'IL' as State, teacherid, 26 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --27 insert into tblEunitActivationDates select 'IL' as State, teacherid, 27 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --28 insert into tblEunitActivationDates select 'IL' as State, teacherid, 28 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --29 insert into tblEunitActivationDates select 'IL' as State, teacherid, 29 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --30 insert into tblEunitActivationDates select 'IL' as State, teacherid, 30 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --31 insert into tblEunitActivationDates select 'IL' as State, teacherid, 31 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --34 insert into tblEunitActivationDates select 'IL' as State, teacherid, 34 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --35 insert into tblEunitActivationDates select 'IL' as State, teacherid, 35 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --37 insert into tblEunitActivationDates select 'IL' as State, teacherid, 37 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --41 insert into tblEunitActivationDates select 'IL' as State, teacherid, 41 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --51 insert into tblEunitActivationDates select 'IL' as State, teacherid, 51 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --54 insert into tblEunitActivationDates select 'IL' as State, teacherid, 54 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --56 insert into tblEunitActivationDates select 'IL' as State, teacherid, 56 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --57 insert into tblEunitActivationDates select 'IL' as State, teacherid, 57 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --58 insert into tblEunitActivationDates select 'IL' as State, teacherid, 58 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --68 insert into tblEunitActivationDates select 'IL' as State, teacherid, 68 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --69 insert into tblEunitActivationDates select 'IL' as State, teacherid, 69 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --81 insert into tblEunitActivationDates select 'IL' as State, teacherid, 81 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --82 insert into tblEunitActivationDates select 'IL' as State, teacherid, 82 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --83 insert into tblEunitActivationDates select 'IL' as State, teacherid, 83 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active';

--Backup tblCSATActivationDates table select * into tblCSATActivationDates_100114 from tblCSATActivationDates

--Delete old records to prepare for new records delete from tblCSATActivationDates where TeacherID IN ( select teacherid from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active' )

--26 insert into tblCSATActivationDates select 'IL' as State, teacherid, 26 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --27 insert into tblCSATActivationDates select 'IL' as State, teacherid, 27 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --28 insert into tblCSATActivationDates select 'IL' as State, teacherid, 28 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --29 insert into tblCSATActivationDates select 'IL' as State, teacherid, 29 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --30 insert into tblCSATActivationDates select 'IL' as State, teacherid, 30 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --31 insert into tblCSATActivationDates select 'IL' as State, teacherid, 31 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --34 insert into tblCSATActivationDates select 'IL' as State, teacherid, 34 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --35 insert into tblCSATActivationDates select 'IL' as State, teacherid, 35 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --37 insert into tblCSATActivationDates select 'IL' as State, teacherid, 37 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --41 insert into tblCSATActivationDates select 'IL' as State, teacherid, 41 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --51 insert into tblCSATActivationDates select 'IL' as State, teacherid, 51 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --54 insert into tblCSATActivationDates select 'IL' as State, teacherid, 54 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --56 insert into tblCSATActivationDates select 'IL' as State, teacherid, 56 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --57 insert into tblCSATActivationDates select 'IL' as State, teacherid, 57 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --58 insert into tblCSATActivationDates select 'IL' as State, teacherid, 58 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --68 insert into tblCSATActivationDates select 'IL' as State, teacherid, 68 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --69 insert into tblCSATActivationDates select 'IL' as State, teacherid, 69 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --81 insert into tblCSATActivationDates select 'IL' as State, teacherid, 81 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --82 insert into tblCSATActivationDates select 'IL' as State, teacherid, 82 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active'; --83 insert into tblCSATActivationDates select 'IL' as State, teacherid, 83 as CDID, '2014-01-01' as ActivateDate, '2015-07-01' as DeactivateDate from tblTeacher t join tblSchool s on t.SchoolID = s.SchoolID where s.State = 'IL' and t.Focus = 'CTE' and t.Status = 'Active';

[mnichols13]

I checked several teachers and they all looked good.