Skip to content

Instantly share code, notes, and snippets.

@othtim

othtim/contact_reimport.sql

Last active August 29, 2015 13:57
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save othtim/9904577 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save othtim/9904577 to your computer and use it in GitHub Desktop.
Download ZIP
Trinity / RENWEB conversion
Raw
contact_reimport.sql
drop table #contactrelation
drop table #contact
drop table #location
declare @ischoolid int
set @ischoolid = 1
create table #contact (
iIndex int identity,
StudentID int,
ParentID int,
cLastName varchar(100),
cFirstName varchar(100),
cHomePhone varchar(100),
iSchoolID int, --gonna make this all trinity
mEmail varchar(100),
mCellPhone varchar(100),
mWorkPhone varchar(100),
istudentid int
)
create table #contactrelation (
StudentID int,
ParentID int,
iLV_RelationID varchar(100), --we have to convert this to a lookup later
iContactPriority int,
lLivesWithStudent int,
lMailTo int,
iSchoolID int, --gonna make this all trinity
mComment varchar(100),
istudentid int
)
create table #location (
StudentID int,
ParentID int,
--cApartment varchar(100),
--cHouseNo varchar(100),
cStreet varchar(100),
--iLV_QuadrantID int, --gonna make this blank anyways
iLV_CityID varchar(100),
iLV_RegionID varchar(100), --gonna make this all AB
iLV_CountryID varchar(100), --gonna make this all canada
cPostalCode varchar(100),
cPhone varchar(100),
istudentid int,
)
insert into #contact
select
parent_student.StudentID,
isnull(Parent_Student.ParentID,0),
p_contact.LastName,
p_contact.FirstName,
isnull(p_contact.HomePhone,''),
1,
isnull(p_contact.Email,''),
isnull(p_contact.CellPhone,''),
isnull(p_contact.WorkPhone,''),
s.istudentid
from Person p_contact
left join parent_student
on Parent_Student.ParentID = p_contact.Personid
left join person_student p_student
on parent_student.studentid = p_student.studentid
left join Person p
on p.personid = p_student.studentid
left join lwca_live.dbo.student s
on p.Birthdate = s.dBirthdate
where p.LastName = s.cLastName
and p.FirstName = s.cfirstname
insert into #contactrelation
select
person_student.studentid,
isnull(parent_student.ParentID,0),
parent_student.Relationship,
1, --sort order
0, --lives with, we will set this dynamically later
isnull(parent_student.Correspondence,0),
1,
parent.Note,
s.istudentid
from person parent
left join parent_student
on Parent_Student.ParentID = parent.Personid
left join Person_Student
on Person_Student.StudentID = parent_student.StudentID
left join Person p
on p.PersonID = person_student.StudentID
left join lwca_live.dbo.student s
on s.dBirthdate = p.birthdate
where p.lastname = s.clastname
and p.firstname = s.cfirstname
insert into #location
select
parent_student.StudentID,
Parent_Student.ParentID,
isnull(a.Address1,''),
isnull(a.City,''),
isnull(a.State,''),
isnull(a.Country,''),
isnull(a.ZIP,''),
isnull(p_contact.HomePhone,''),
s.istudentid
from parent_student
left join Person p_contact
on p_contact.PersonID = parent_student.ParentID
left join Address a
on a.AddressID = p_contact.AddressID
left join Person_Student
on Person_Student.StudentID = parent_student.StudentID
left join Person p
on p.PersonID = person_student.StudentID
left join lwca_live.dbo.student s
on s.dBirthdate = p.birthdate
where p.lastname = s.clastname
and p.firstname = s.cfirstname
update #contactrelation set iLV_RelationID = 'Other'
where iLV_RelationID not in ('Mother','Father','Grandparent')
declare @v_t_studentIndex int
declare cStudent cursor for
select istudentid from lwca_live.dbo.student
open cStudent
fetch cStudent into @v_t_studentIndex
while @@fetch_status=0
begin
declare @v_t_iIndex int --index of the contact record we are working on
declare @v_t_StudentID int --studentID we are working on
declare @v_t_cGovNumber varchar(20) --the student government number
declare @v_p_iLV_RelationID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_CityID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_StateID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_CountryID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iContactID int --the iContactID on the lwca_live side
declare @v_p_iLocationID int --the iLocationID on the lwca_live side
declare @cursor_ContactUpdate int --the icontactid of the contacts we need to update. per (renweb parent) per (lwca student).
declare @cursor_ContactUpdate_location int --the ilocationid of the contact we are updating
----------------------------
--contact loop
declare cContact cursor for
select #contact.iIndex from #contact
join #contactrelation
on #contact.ParentID = #contactrelation.ParentID
where #contact.istudentid = @v_t_studentIndex
and #contactrelation.istudentid = @v_t_studentIndex
open cContact
fetch cContact into @v_t_iIndex
while @@fetch_status=0
begin
----debug
--select * from #contact
--join #contactrelation
-- on #contact.ParentID = #contactrelation.ParentID and #contact.StudentID = #contactrelation.StudentID
--where #contact.iIndex = @v_t_iIndex
--and #contact.istudentid = @v_t_studentIndex
set @v_p_iLV_CityID = 0
set @v_p_iLV_StateID = 1
set @v_p_iLV_CountryID = 1
set @v_p_iLV_RelationID =
(select ilookupvaluesid from lwca_live.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from lwca_live.dbo.datatypes where cName like 'Relation')
and cName like
(select #contactrelation.iLV_RelationID from #contact
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contact.istudentid = @v_t_studentIndex
and #contactrelation.istudentid = @v_t_studentIndex) collate database_default )
set @v_p_iLV_CityID =
(select ilookupvaluesid from lwca_live.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from lwca_live.dbo.datatypes where cName like 'City')
and cName like
(select #location.iLV_CityID from #location
join #contact
on #location.ParentID = #contact.ParentID
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.istudentid = @v_t_studentIndex
and #location.istudentid = @v_t_studentIndex) collate database_default )
set @v_p_iLV_StateID =
(select ilookupvaluesid from lwca_live.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from lwca_live.dbo.datatypes where cName like 'State')
and cName like
(select #location.iLV_RegionID from #location
join #contact
on #location.ParentID = #contact.ParentID
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.istudentid = @v_t_studentIndex
and #location.StudentID = @v_t_StudentID) collate database_default )
-- now we check if the contacts exists already, and if there is more than one of them.
-- if the contact doesn't exist we add it.
-- if the contact exists then we will update
if(
(select live_c.icontactid
from lwca_live.dbo.student live_S
left join lwca_live.dbo.contactrelation live_CR
on live_S.iStudentID = live_CR.istudentid
left join lwca_live.dbo.Contact live_C
on live_CR.icontactid = live_C.iContactID
where live_S.istudentid = @v_t_studentIndex
and live_c.cLastName = (select cLastname from #contact where #contact.iIndex = @v_t_iIndex) collate database_default
and live_c.cFirstName = (select cfirstname from #contact where #contact.iIndex = @v_t_iIndex) collate database_default
and live_cr.iLV_RelationID in (select ilookupvaluesid from lwca_live.dbo.LookupValues where cName in ('Mother','Father','Grandparent','Other')))
is NULL)
BEGIN
--debug
select
@v_t_iIndex as '#contact_iIndex',
*
from #contact where iIndex = @v_t_iIndex
insert into lwca_live.dbo.location (
cStreet,
iLV_CityID,
iLV_RegionID,
iCountryID,
cPostalCode,
cPhone,
iSchoolID)
select
isnull(cStreet,'') as cStreet,
isnull(@v_p_iLV_CityID,0) as iLV_CityID,
isnull(@v_p_iLV_StateID,0) as iLV_RegionID,
@v_p_iLV_CountryID as iCountryID,
isnull(cPostalCode,'') as cPostalCode,
isnull(cPhone,'') as cPhone,
1
from #location
join #contact
on #contact.ParentID = #location.ParentID
where #contact.iStudentID = @v_t_studentIndex
and #location.iStudentID = @v_t_studentIndex
and #contact.iIndex = @v_t_iIndex
--this should grab the last inserted locationid
set @v_p_iLocationID = (SELECT SCOPE_IDENTITY())
--select top 1 * from lwca_live.dbo.location order by ilocationid desc
insert into lwca_live.dbo.Contact (
cLastName,
cFirstName,
iLocationID,
cBusPhone,
iSchoolID,
mEmail,
mCellphone)
select
isnull(cLastname,'') as cLastname,
isnull(cFirstname,'') as cFirstname,
@v_p_iLocationID,
isnull(cHomePhone,'') as cBusPhone,
1,
isnull(mEmail,'') as mEmail,
isnull(mCellPhone,'') as mCellPhone
from #contact
where #contact.iIndex = @v_t_iIndex
and #contact.iStudentID = @v_t_studentIndex
--
--grab last inserted icontactid
set @v_p_iContactID = (SELECT SCOPE_IDENTITY())
insert into lwca_live.dbo.UserContact (iContactID, iSchoolID) values(@v_p_iContactID, @iSchoolID)
--select * from lwca_live.dbo.contact where iContactID = @v_p_iContactID
insert into lwca_live.dbo.ContactRelation (iContactID, iStudentID, iLV_RelationID, iContactPriority, lLivesWithStudent, lMail, iSchoolID, mComment)
select
@v_p_iContactID as iContactID,
@v_t_studentIndex as iStudentID,
isnull(@v_p_iLV_RelationID,0) as iLV_RelationID,
isnull(iContactPriority,0) as iContactPriority,
0 as lLivesWithStudent,
isnull(lMailTo,0) as lMail,
1 as iSchoolID,
ISNULL(mComment,0) as mComment
from #contactrelation
join #contact
on #contact.ParentID = #contactrelation.ParentID
and #contact.iIndex = @v_t_iIndex
where #contactrelation.iStudentID = @v_t_studentIndex
print 'hi'
END
else --ie if the contact EXISTS. we need to update, not add.
BEGIN
--step through every contact we have to update.
declare cContactUpdate cursor for
(select live_c.icontactid
from lwca_live.dbo.student live_S
left join lwca_live.dbo.contactrelation live_CR
on live_S.iStudentID = live_CR.istudentid
left join lwca_live.dbo.Contact live_C
on live_CR.icontactid = live_C.iContactID
where live_S.istudentid = @v_t_studentIndex
and live_c.cLastName = (select cLastname from #contact where #contact.iIndex = @v_t_iIndex) collate database_default
and live_c.cFirstName = (select cfirstname from #contact where #contact.iIndex = @v_t_iIndex) collate database_default
and live_cr.iLV_RelationID in (select ilookupvaluesid from lwca_live.dbo.LookupValues where cName in ('Mother','Father','Grandparent','Other')))
open cContactUpdate
fetch cContactUpdate into @cursor_ContactUpdate
while @@fetch_status=0
begin
set @cursor_ContactUpdate_location = (select ilocationid from lwca_live.dbo.contact where iContactID = @cursor_ContactUpdate)
--select @cursor_ContactUpdate_location, @cursor_ContactUpdate
--need to update location, busphone, email, and cellphone.
--if there were any changes, print for debug before applying
select
@v_t_iIndex,
mWorkPhone,
mEmail,
mCellphone
from #Contact
where #Contact.iIndex = @v_t_iIndex
and mWorkPhone != (select mWorkPhone from lwca_live.dbo.Contact where iContactID = @cursor_ContactUpdate)
and mEmail != (select mEmail from lwca_live.dbo.Contact where iContactID = @cursor_ContactUpdate) collate database_default
and mCellPhone != (select mCellPhone from lwca_live.dbo.Contact where iContactID = @cursor_ContactUpdate) collate database_default
update lwca_live.dbo.Contact set cBusPhone = (select mWorkPhone from #Contact where iIndex = @v_t_iIndex) where iContactID = @cursor_ContactUpdate
update lwca_live.dbo.Contact set mEmail = (select mEmail from #Contact where iIndex = @v_t_iIndex) where iContactID = @cursor_ContactUpdate
update lwca_live.dbo.Contact set mCellphone = (select mCellPhone from #Contact where iIndex = @v_t_iIndex) where iContactID = @cursor_ContactUpdate
update lwca_live.dbo.location set cApartment = (select cApartment from #location
join #contact
on #contact.ParentID = #location.ParentID
where iIndex = @v_t_iIndex
and #contact.StudentID = #location.StudentID
) where iLocationID = @cursor_ContactUpdate_location
update lwca_live.dbo.location set cHouseNo = (select cHouseNo from #location
join #contact
on #contact.ParentID = #location.ParentID
where iIndex = @v_t_iIndex
and #contact.StudentID = #location.StudentID
) where iLocationID = @cursor_ContactUpdate_location
update lwca_live.dbo.location set cStreet = (select cStreet from #location
join #contact
on #contact.ParentID = #location.ParentID
where iIndex = @v_t_iIndex
and #contact.StudentID = #location.StudentID
) where iLocationID = @cursor_ContactUpdate_location
update lwca_live.dbo.location set cPostalcode = (select cPostalCode from #location
join #contact
on #contact.ParentID = #location.ParentID
where iIndex = @v_t_iIndex
and #contact.StudentID = #location.StudentID
) where iLocationID = @cursor_ContactUpdate_location
update lwca_live.dbo.location set cPhone = (select cApartment from #location
join #contact
on #contact.ParentID = #location.ParentID
where iIndex = @v_t_iIndex
and #contact.StudentID = #location.StudentID
) where iLocationID = @cursor_ContactUpdate_location
fetch next from cContactUpdate into @cursor_ContactUpdate
end
close cContactUpdate
deallocate cContactUpdate
END
fetch next from cContact into @v_t_iIndex
end
close cContact
deallocate cContact
fetch next from cStudent into @v_t_studentIndex
end
close cStudent
deallocate cStudent
Raw
trinity_renweb.sql
--will have to replace 'palliser' with whatever the current database name is.
use lwca
BEGIN TRANSACTION
declare @iSchoolID int = 1
declare @iTrackID int = 1
--------------------------------------------------------------------------------------------------------------
--COURSES/CLASSES---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
drop table #courses
drop table #classes
create table #courses (
iIndex int identity,
iCourseID int, --CourseID
cName varchar(30), --Title
cCourseCode varchar(20), --Abbreviation
Credit int, --This will turn into low cred and high cred
cDepartment varchar(30), --Department
--Grade, --Low Grade and High Grade can probably be determined by the "Elementary", "MidleSchool", and "High School" fields
lCore bit --ReportCard
)
--select * from palliser.dbo.course
--select * from courses
create table #classes (
iIndex int identity,
ClassID int, --ClassID
iClassID int, --iClassID from sl db
iCourseID int, --CourseID
cName varchar(30), --Name
cSection varchar(10), --Section
YearID int, --we can use this to determine track
MaxSize int,
)
--select * from palliser.dbo.class
--select * from classes
----------------------------------------------------------------
insert into #courses
select
c.CourseID,
left(ltrim(rtrim(c.Title)),30),
left(ltrim(rtrim(c.Abbreviation)),20),
c.Credits,
left(ltrim(rtrim(c.Department)),30),
c.ReportCard
from Courses c
insert into #classes
select
c.ClassID,
0,
c.CourseID,
left(ltrim(rtrim(c.Name)),30),
left(ltrim(rtrim(c.Section)),10),
0,
MaxSize
from Classes c
where c.YearID = 257
------------------------------------------------------------------------------------------------
declare @v_t_CourseiIndex int --index of the course record we are working on
declare @v_t_ClassiIndex int --index of the course record we are working on
declare @v_p_CourseiIndex int --index of the course on the palliser side
declare @v_p_ClassiIndex int --index of the course on the palliser side
declare cCourses cursor for
select iIndex from #courses
open cCourses
fetch cCourses into @v_t_CourseiIndex
while @@fetch_status=0
begin
insert into palliser.dbo.course
select
c.cName as 'cName',
c.cCourseCode as 'cCourseCode',
0 as 'iGovCourseID', --might have to make this look it up
c.cCourseCode as 'cGovernmentCode',
c.cDepartment as 'cDepartment',
0.00 as 'nFee',
'' as 'iLow_GradesID',
'' as 'iHigh_GradesID',
c.Credit as 'nLowCredit',
c.Credit as 'nHighCredit',
'' as 'mComment',
0 as 'iDepartmentExamsID',
4 as 'iLV_LanguageID', --english in the palliser db
0 as 'nAverageWeight',
1 as 'lOfferedInSchool',
0 as 'lSchoolExam',
0 as 'cLevel',
0 as 'lCore',
50 as 'nMinMark',
0 as 'lMasterCourse',
0 as 'lMaster_CourseID',
0 as 'iOrder',
0 as 'iLV_ExternalCredentialsID',
0 as 'lExcludeFromAve',
0 as 'iMarkLegendID',
0 as 'nGpaRuleNumber',
0 as 'iLV_SubjectID',
0 as 'iLV_DepartmentID',
0 as 'lWaivedCourse',
0 as 'lDuplicateCredit',
0 as 'lConflictMatrix',
0 as 'lTallyList',
0 as 'iLV_GovGradesID',
0 as 'iEffortLegendID',
0 as 'iObjectiveEffortLegendID',
0 as 'lStudyHall',
0 as 'lOffCampus',
@iSchoolID as 'iSchoolID', --This will have to be updated later.
0 as 'iUserField3'
from #courses c
where c.iIndex = @v_t_CourseiIndex
set @v_p_CourseiIndex = (select top 1 icourseid from palliser.dbo.Course order by iCourseID desc)
declare cClasses cursor for
select #classes.iIndex from #classes
where #classes.iCourseID =
(select iCourseID from #courses where iIndex = @v_t_CourseiIndex)
open cClasses
fetch cClasses into @v_t_ClassiIndex
while @@fetch_status=0
begin
insert into palliser.dbo.class
select
@v_p_CourseiIndex as 'iCourseID',
c.cName as 'cName',
c.cSection as 'cSection',
@iTrackID as 'iTrackID',
0 as 'iIdealCapacity',
0.00 as 'nFree',
0 as 'nCredit',
0 as 'iLV_GenderID',
0 as 'iLow_GradesID',
0 as 'iHigh_GradesID',
'' as 'mComment',
4 as 'iLV_LanguageID', --english in the palliser db
0 as 'iDepartmentExamsID',
0 as 'lExamMark',
0 as 'lCalculatedMark',
1 as 'lFinalMark',
0 as 'iMarkType',
0 as 'lSchoolExam',
0 as 'iMarkLegendID',
0 as 'iSchoolID',
0 as 'iRequestID',
0 as 'iLV_SessionID',
0 as 'nExamMonth',
0 as 'nExamWeight',
'' as 'dStartDate',
'' as 'dEndDate',
0 as 'iMaster_ClassID',
0 as 'lMasterClass',
'' as 'dExamDate',
0 as 'iOrder',
'' as 'iUserField3',
'' as 'iUserField2',
0 as 'lScheduled',
0 as 'lEOYMark',
0 as 'lMoveEOYToHistory',
0 as 'lExcludeEOYGPA',
0 as 'lAssignCreditEOY',
0 as 'lCalcEOYMark',
0 as 'iLV_EOYSessionID',
0 as 'iSeatingRows',
0 as 'iSeatingColumns',
0 as 'lUseStaffMax',
0 as 'iDefault_StaffID',
0 as 'iDefault_RoomID',
MaxSize as 'iMaxCapacity',
0 as 'lAcademic',
0 as 'iHomeRoomID',
0 as 'lRegentMark',
0 as 'iUserField4',
0 as 'iUserField5',
0 as 'iUserField6',
0 as 'iRoomLayoutID'
from #classes c
where c.iIndex = @v_t_ClassiIndex
--fill in the iClassID that was just generated, we will need it later for enrollments
update #classes
set iClassID = (SELECT SCOPE_IDENTITY())
where iIndex = @v_t_ClassiIndex
fetch next from cClasses into @v_t_ClassiIndex
end
close cClasses
deallocate cClasses
fetch next from cCourses into @v_t_CourseiIndex
end
close cCourses
deallocate cCourses
--cleanup
update palliser.dbo.Course set iLow_GradesID = 13
update palliser.dbo.Course set iHigh_gradesid = 10
update palliser.dbo.Class set iLow_GradesID = 13
update palliser.dbo.Class set iHigh_gradesid = 10
--------------------------------------------------------------------------------------------------------------
--STUDENT-----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------
drop table #student
drop table #studentLocation
drop table #studentStatus
drop table #contact
drop table #contactrelation
drop table #location
create table #student (
iIndex int identity,
StudentID int,
SchoolID varchar(50), --cgovernmentnumber
cLastName varchar(50),
cFirstName varchar(50),
cMiddleName varchar(50),
NickName varchar(50),
dBirthDate smalldatetime,
Gender varchar(10),
Ethnicity varchar(50),
Citizenship varchar(100),
PrimaryLanguage varchar(50),
mEmail varchar(100),
HomePhone varchar(20),
CellPhone varchar(20),
Status varchar(50),
NextStatus varchar(50),
EnrolDate smalldatetime,
WithdrawDate smalldatetime,
WithdrawReason varchar(100),
GradeLevel varchar(10),
NextGradeLevel varchar(10)
)
create table #studentLocation (
iIndex int identity,
StudentID int,
SchoolID varchar(50),
--cApartment varchar(100),
--cHouseNo varchar(100),
cStreet varchar(100),
--iLV_QuadrantID int, --gonna make this blank anyways
iLV_CityID varchar(100),
iLV_RegionID varchar(100), --gonna make this all AB
iLV_CountryID varchar(100), --gonna make this all canada
cPostalCode varchar(100),
cPhone varchar(100),
iSchoolID int --gonna make this all trinity
)
create table #studentStatus (
iIndex int identity,
StudentID int,
dInDate smalldatetime,
dOutDate smalldatetime,
iLV_VerificationID int,
lOutsideStatus int,
iSchoolID int,
iLV_InStatusValueID int,
iLV_OutStatusValueID int,
)
----------------------------------------------------------------
create table #contact (
iIndex int identity,
StudentID int,
ParentID int,
cLastName varchar(100),
cFirstName varchar(100),
cHomePhone varchar(100),
iSchoolID int, --gonna make this all trinity
mEmail varchar(100),
mCellPhone varchar(100),
SchoolID varchar(100) --studentid from renweb
)
create table #contactrelation (
iStudentID int,
StudentID int,
ParentID int,
iLV_RelationID varchar(100), --we have to convert this to a lookup later
iContactPriority int,
lLivesWithStudent int,
lMailTo int,
iSchoolID int, --gonna make this all trinity
mComment varchar(100),
SchoolID varchar(100) --studentid from renweb
)
create table #location (
StudentID int,
ParentID int,
--cApartment varchar(100),
--cHouseNo varchar(100),
cStreet varchar(100),
--iLV_QuadrantID int, --gonna make this blank anyways
iLV_CityID varchar(100),
iLV_RegionID varchar(100), --gonna make this all AB
iLV_CountryID varchar(100), --gonna make this all canada
cPostalCode varchar(100),
cPhone varchar(100),
iSchoolID int --gonna make this all trinity
)
---------------------------------------------------------------------------------------------------------------------------------
insert into #student
select
p_s.StudentID,
p_s.SchoolID, --needs to be non-zero
p.LastName,
p.FirstName,
p.MiddleName,
p.NickName,
p.BirthDate,
p.Gender,
p.Ethnicity,
p.Citizenship,
p.PrimaryLanguage,
p.Email,
p.HomePhone,
p.CellPhone,
p_s.Status,
p_s.NextStatus,
p_s.EnrollDate,
p_s.WithdrawDate,
p_s.WithdrawReason,
p_s.GradeLevel,
p_s.NextGradeLevel
from person_student p_s
LEFT join person p
on p_s.studentid = p.PersonID
insert into #studentLocation
select
p_s.StudentID,
p_s.SchoolID,
a.Address1,
a.City,
a.State,
a.Country,
a.ZIP,
p.HomePhone,
@iSchoolID
from person_student p_s
LEFT join person p
on p_s.studentid = p.PersonID
LEFT join Address a
on p.AddressID = a.AddressID
insert into #studentStatus
select
p_s.StudentID,
isnull(p_s.EnrollDate,0),
isnull(p_s.WithdrawDate,0),
0,
0,
@iSchoolID,
0,
0
from person_student p_s
LEFT join person p
on p_s.studentid = p.PersonID
LEFT join Address a
on p.AddressID = a.AddressID
---------------------------------------------------------------------------------------------------------------------------------
insert into #contact
select
ec.StudentID,
isnull(Parent_Student.ParentID,0),
ec.LastName,
ec.FirstName,
ec.HomePhone,
@iSchoolID as iSchoolID,
ec.Email,
ec.CellPhone,
ec.StudentID
from emergencycontact ec
left join parent_student
on Parent_Student.ParentID = ec.RefID AND parent_student.StudentID = ec.StudentID
insert into #contactrelation
select
p_Student.istudentid,
ec.studentid,
isnull(parent_student.ParentID,0),
ec.Relationship,
ec.SortOrder,
'',
isnull(parent_student.Correspondence,0),
'' as iSchoolid,
ec.Note,
p_s.SchoolID --student gov number
from emergencycontact ec
left join Person_Student p_s
on p_s.StudentID = ec.studentid
left join parent_student
on Parent_Student.ParentID = ec.RefID AND parent_student.StudentID = ec.StudentID
left join Person p
on p.PersonID = ec.StudentID
left join palliser.dbo.Student p_Student
on p_Student.cGovernmentNumber = p_s.SchoolID
where p_student.clastname = p.lastname
and p_student.cfirstname = p.firstname
and p_s.SchoolID != ''
insert into #location
select
ec.StudentID,
Parent_Student.ParentID,
a.Address1,
a.City,
a.State,
a.Country,
a.ZIP,
ec.HomePhone,
''
from emergencycontact ec
left join parent_student
on Parent_Student.ParentID = ec.RefID AND parent_student.StudentID = ec.StudentID
left join Person p
on p.PersonID = ec.RefID
left join Address a
on a.AddressID = p.AddressID
------------------------------------------------------------------------------------------------------------------------------------------------
--need to update students without a gov't number to have a gov't number?
----
declare @v_t_studentIndex int --index of the student record we are working on
declare @studentiStudentID int --the iStudentid of the student we are working on in palliser
declare @v_t_iGenderID int
declare @v_t_iGradesID int
declare @v_t_lCurrent int
declare @student_iLV_CityID int
declare @student_iLV_StateID int
declare @student_iLV_CountryID int
declare @student_iLocationID int
--we have to figure out what statuses we should put in
declare @student_iLV_InStatusValueID int
declare @student_iLV_OutStatusValueID int
--delete from palliser.dbo.lookupvalues where cname in ('Generated Enrolled', 'Generated Withdrawn')
--insert our generated enroll and withdrawn
insert into palliser.dbo.lookupvalues (cName, cCode, idatatypesID, iSchoolID, lInactive, nColor, iLinkID, mComment)
values('Generated Enrolled','GE',1668,@ischoolID,0,0,0,'')
if( exists (select cName from palliser.dbo.lookupvalues where cName like 'Generated Enrolled' and idatatypesid = 1668) )
set @student_iLV_InStatusValueID = (select iLookupValuesID from palliser.dbo.lookupvalues where cName like 'Generated Enrolled' and idatatypesid = 1668)
else
set @student_iLV_InStatusValueID = (SELECT SCOPE_IDENTITY())
insert into palliser.dbo.lookupvalues (cName, cCode, idatatypesID, iSchoolID, lInactive, nColor, iLinkID, mComment)
values('Generated Withdrawn','GW',1669,@ischoolID,0,0,0,'')
if( exists (select cName from palliser.dbo.lookupvalues where cName like 'Generated Withdrawn' and idatatypesid = 1669) )
set @student_iLV_OutStatusValueID = (select iLookupValuesID from palliser.dbo.lookupvalues where cName like 'Generated Withdrawn' and idatatypesid = 1669)
else
set @student_iLV_OutStatusValueID = (SELECT SCOPE_IDENTITY())
-------------------------
declare cStudent cursor for
select iIndex from #student
open cStudent
fetch cStudent into @v_t_studentIndex
while @@fetch_status=0
begin
set @student_iLV_CityID = NULL
set @student_iLV_CityID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'City') --should be 37
and cName like
(select #studentlocation.iLV_CityID from #studentLocation
join #student
on #student.studentid = #studentLocation.studentid
where #student.iIndex = @v_t_studentIndex) collate database_default)
--if the value doesn't exist in the table we need to add it
if (@student_iLV_CityID IS NULL)
insert into palliser.dbo.lookupvalues (cName, cCode, iDataTypesID, iSchoolID, lInactive, nColor, iLinkID, mComment)
(select left(#studentLocation.iLV_CityID,59) as 'cName',
left(#studentLocation.iLV_CityID,9) as 'cCode',
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'City') as 'iDataTypesID',
@iSchoolID as 'iSchoolID',
0 as 'lInactive',
0 as 'nColor',
0 as 'iLinkID',
'' as 'mComment'
from #studentlocation
join #student
on #student.studentid = #studentLocation.studentid
where #student.iIndex = @v_t_studentIndex)
set @student_iLV_StateID = NULL
set @student_iLV_StateID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'State') --should be 38
and cName like
(select #studentlocation.iLV_RegionID from #studentLocation
join #student
on #student.studentid = #studentLocation.studentid
where #student.iIndex = @v_t_studentIndex) collate database_default)
if (@student_iLV_StateID IS NULL)
insert into palliser.dbo.lookupvalues (cName, cCode, iDataTypesID, iSchoolID, lInactive, nColor, iLinkID, mComment)
(select left(#studentLocation.iLV_RegionID,59) as 'cName',
left(#studentLocation.iLV_RegionID,9) as 'cCode',
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'State') as 'iDataTypesID',
@iSchoolID as 'iSchoolID',
0 as 'lInactive',
0 as 'nColor',
0 as 'iLinkID',
'' as 'mComment'
from #studentlocation
join #student
on #student.studentid = #studentLocation.studentid
where #student.iIndex = @v_t_studentIndex)
--this is easier
set @student_iLV_CountryID = 1
-- (select ilookupvaluesid from palliser.dbo.LookupValues
-- where iDataTypesID =
-- (select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Country')
-- and cName like
-- (select #studentlocation.iLV_CityID from #studentLocation
-- join #student
-- on #student.studentid = #studentLocation.studentid
-- where #student.iIndex = @v_t_studentIndex) collate database_default)
--add location for this student
insert into palliser.dbo.location
select
'' as cApartment,
'' as cHouseNo,
isnull(cStreet,'') as cStreet,
'' as iLV_QuadrantID,
isnull(@student_iLV_CityID,0) as iLV_CityID,
isnull(@student_iLV_StateID,0) as iLV_RegionID,
isnull(@student_iLV_CountryID,0) as iLV_CountryID,
isnull(cPostalCode,'') as cPostalCode,
isnull(cPhone,'') as cPhone,
'' as lUnlistedPhone,
@iSchoolID as iSchoolID
from #studentLocation
join #student
on #student.StudentID = #studentLocation.StudentID
where #student.iIndex = @v_t_studentIndex
--this should grab the last inserted locationid
set @student_iLocationID = (SELECT SCOPE_IDENTITY())
---------------------------------------
set @v_t_iGenderID = (select iLookupValuesID from palliser.dbo.lookupValues
where cName like
(select Gender from #student where iIndex like @v_t_studentIndex ) COLLATE database_default
and iDataTypesID = 28 )
if (@v_t_iGenderID IS NULL)
insert into palliser.dbo.lookupvalues (cName, cCode, iDataTypesID, iSchoolID, lInactive, nColor, iLinkID, mComment)
(select left(#student.Gender,59) as 'cName',
left(#student.Gender,9) as 'cCode',
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Gender') as 'iDataTypesID',
@iSchoolID as 'iSchoolID',
0 as 'lInactive',
0 as 'nColor',
0 as 'iLinkID',
'' as 'mComment'
from #student
where #student.iIndex = @v_t_studentIndex)
--select ilv_genderid from palliser.dbo.student
--where ( ilv_genderid = 0
--or palliser.dbo.GetLookupValue(iLV_GenderID) = ''
--or palliser.dbo.GetLookupValue(iLV_GenderID) like 0)
set @v_t_iGradesID = (select iGradesID from palliser.dbo.grades
where cName like
(select GradeLevel from #student where iIndex like @v_t_studentIndex ) COLLATE database_default)
--we will need to reorder grades in data cleanup later
--we cant insert a null order, so always grab MAX() + 1
if (@v_t_iGradesID IS NULL)
if ((select max(iOrder) from palliser.dbo.grades where ischoolid = @iSchoolID) is NULL)
insert into palliser.dbo.grades (cName, cCode, iOrder, iSchoolID)
(select left(#student.GradeLevel,60) as 'cName',
left(#student.GradeLevel,10) as 'cCode',
1 as 'iOrder',
@iSchoolID as 'iSchoolID'
from #student
where #student.iIndex = @v_t_studentIndex)
else
insert into palliser.dbo.grades (cName, cCode, iOrder, iSchoolID)
(select left(#student.GradeLevel,60) as 'cName',
left(#student.GradeLevel,10) as 'cCode',
(select MAX(iOrder) + 1 from palliser.dbo.grades where ischoolid = @iSchoolID) as 'iOrder',
@iSchoolID as 'iSchoolID'
from #student
where #student.iIndex = @v_t_studentIndex)
if((select Status from #student where iIndex = @v_t_studentIndex) = 'Enrolled')
set @v_t_lCurrent = 1
else
set @v_t_lCurrent = 0
--determine if the student is lCurrent or not
--select * from #student
--first thing is insert the student record as much as we can
insert into palliser.dbo.student
select
s.StudentID as 'cStudentNumber',
s.cFirstName as 'cFirstName',
s.cLastName as 'cLastName',
s.cFirstName as 'cLegalFirstName',
s.cMiddleName as 'cLegalMiddleName',
s.cLastName as 'cLegalLastName',
isnull(@v_t_iGenderID,0) as 'iLV_GenderID',
isnull(@v_t_iGradesID,0) as 'iGradesID',
isnull(s.dBirthDate,0) as 'dBirthDate',
@iTrackID as 'iTrackID', --will fill later
@iSchoolID as 'iSchoolID',
'' as 'mMedical',
'' as 'cPicturePath',
isnull(s.SchoolID,'') as 'cGovernmentNumber',
'' as 'cLockerNumber',
'' as 'cLockerCombination',
'' as 'cHealthCareNumber', -- will fill later
0 as 'iHomeroomID',
0 as 'iAlt_HomeroomID',
@v_t_lCurrent as 'lCurrent',
0 as 'iLV_NextStatusID',
0 as 'iNext_GradesID',
0 as 'iNext_HomeroomID',
0 as 'iNext_Counselor_StaffID',
0 as 'iNext_Advisor_StaffID',
0 as 'iNext_SchoolID',
0 as 'iNext_TrackID',
0 as 'iAdvisor_staffID',
0 as 'iCounselor_StaffID',
'' as 'cBarCodeNumber',
0 as 'nGradYear',
0.00 as 'nFreeCredit',
'' as 'mFeeNotes',
0 as 'iLV_ResidentID',
0 as 'lStudentProtect',
'' as 'mProtectComment',
0 as 'iPrevious_SchoolID',
0 as 'lRetainedGrade',
1 as 'iStatusType',
0 as 'lSped',
0.00 as 'nSeatWidth',
'' as 'cAcademicRecognition',
'' as 'dAcademicRecDate',
'' as 'mAcademicResult',
0 as 'nMealPayType',
0 as 'iLV_WaiverID',
0 as 'iLV_CocurricularStatusID',
0 as 'iLV_CocurriclarPermitID',
'' as 'dNotEligibleUntil',
'' as 'dCocurricularAsOf',
0 as 'iLV_CocurricularRegistrarID',
'' as 'CocurricularProblems',
0 as 'lPriorStatus',
'' as 'gSpedID',
'' as 'mSpedAccessList',
isnull(s.mEmail,'') as 'mEmail',
0 as 'iCurrent_StudentID', --will update later
0 as 'iSend_SchoolID',
0 as 'iLastSpedForm',
0 as 'iTransferID',
0 as 'iFamilyID', --will update later or before
'' as 'cUsername',
'' as 'cPassword',
0 as 'AllowLogin',
0 as 'iActive_StudentStatusID', --we will update later or before
@student_iLocationID as 'iLocationID',
0 as 'lChangePassword',
'' as 'cLDAPName',
isnull(CellPhone,'') as 'mCellPhone',
0 as 'iLV_NextOutStatusID',
'' as 'dLocationEffectiveDate',
'' as 'cSuffix',
0 as 'iHomeroom_RoomLayoutDetailID',
0 as 'iAlt_Homeroom_RoomLayoutDetailID'
from #student s
where s.iIndex = @v_t_studentIndex
set @studentiStudentID = (SELECT SCOPE_IDENTITY())
insert into palliser.dbo.UserStudent (istudentID, iSchoolID) values(@studentiStudentID, @iSchoolID)
if( (select Status from #student where iIndex = @v_t_studentIndex) = 'Enrolled' )
begin
insert into palliser.dbo.studentstatus
select
@studentiStudentID,
dInDate,
dOutDate,
0,
0,
iSchoolID,
@student_iLV_InStatusValueID,
0,
0
from #studentstatus
join #student
on #student.studentid = #studentstatus.studentid
where #student.iindex = @v_t_studentIndex
end
else
begin
insert into palliser.dbo.studentstatus
select
@studentiStudentID,
dInDate,
dOutDate,
0,
0,
iSchoolID,
@student_iLV_InStatusValueID,
@student_iLV_OutStatusValueID,
0
from #studentstatus
join #student
on #student.studentid = #studentstatus.studentid
where #student.iindex = @v_t_studentIndex
end
------------------------------------------------------------------------------------------------------------------------------------
----------------------------
--select * from #contact
--select * from #contactrelation
--select * from #location
declare @v_t_iIndex int --index of the contact record we are working on
declare @v_t_StudentID int --studentID we are working on
declare @v_t_cGovNumber varchar(20) --the student government number
declare @v_p_iLV_RelationID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_CityID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_StateID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iLV_CountryID int --need to translate the varchar in #contact to an ilookupvalueid
declare @v_p_iStudentID int --the iStudentID on the palliser side
declare @v_p_iContactID int --the iContactID on the palliser side
declare @v_p_iLocationID int --the iLocationID on the palliser side
----------------------------
--contact loop
declare cContact cursor for
select #contact.iIndex from #contact
join #contactrelation
on #contact.studentid = #contactrelation.studentid
join #student
on #student.studentid = #contactrelation.studentid
where #student.iIndex = @v_t_studentIndex
open cContact
fetch cContact into @v_t_iIndex
while @@fetch_status=0
begin
set @v_p_iStudentID = (select istudentid from palliser.dbo.Student
where cStudentNumber like
(select studentid from #student where iindex = @v_t_studentIndex))
--before setting title and address values we need to check if this contact has a ParentID of 0
if( (select ParentID from #contact where (iIndex = @v_t_iIndex) and (StudentID = @v_t_StudentID)) = 0)
BEGIN
set @v_p_iLV_RelationID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Relation')
and cName like 'Other')
set @v_p_iLV_CityID = 0
set @v_p_iLV_StateID = 0
set @v_p_iLV_CountryID = 0
END
else
BEGIN
--set some defaults
set @v_p_iLV_RelationID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Relation')
and cName like 'Other')
set @v_p_iLV_CityID = 0
set @v_p_iLV_StateID = 0
set @v_p_iLV_CountryID = 0
set @v_p_iLV_RelationID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Relation')
and cName like
(select #contactrelation.iLV_RelationID from #contact
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.studentid = @v_t_StudentID) collate database_default )
set @v_p_iLV_CityID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'City')
and cName like
(select #location.iLV_CityID from #location
join #contact
on #location.ParentID = #contact.ParentID
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.studentid = @v_t_StudentID
and #location.StudentID = @v_t_StudentID) collate database_default )
set @v_p_iLV_StateID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'State')
and cName like
(select #location.iLV_RegionID from #location
join #contact
on #location.ParentID = #contact.ParentID
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.studentid = @v_t_StudentID
and #location.StudentID = @v_t_StudentID) collate database_default )
set @v_p_iLV_CountryID =
(select ilookupvaluesid from palliser.dbo.LookupValues
where iDataTypesID =
(select TOP 1 iDataTypesID from palliser.dbo.datatypes where cName like 'Country')
and cName like
(select #location.iLV_CountryID from #location
join #contact
on #location.ParentID = #contact.ParentID
join #contactrelation
on #contactrelation.ParentID = #contact.ParentID
where #contact.iIndex = @v_t_iIndex
and #contactrelation.studentid = @v_t_StudentID
and #location.StudentID = @v_t_StudentID) collate database_default )
END
insert into palliser.dbo.location (cStreet, iLV_CityID, iLV_RegionID, iCountryID, cPostalCode, cPhone,iSchoolID)
select
isnull(cStreet,'') as cStreet,
isnull(@v_p_iLV_CityID,0) as iLV_CityID,
isnull(@v_p_iLV_StateID,0) as iLV_RegionID,
isnull(@v_p_iLV_CountryID,0) as iCountryID,
isnull(cPostalCode,'') as cPostalCode,
isnull(cPhone,'') as cPhone,
@iSchoolID as iSchoolID
from #location
join #contact
on #contact.ParentID = #location.ParentID
where #location.StudentID = @v_t_StudentID
and #contact.StudentID = @v_t_StudentID
and #contact.iIndex = @v_t_iIndex
--this should grab the last inserted locationid
set @v_p_iLocationID = (SELECT SCOPE_IDENTITY())
--select top 1 * from palliser.dbo.contact
insert into palliser.dbo.Contact (cLastName, cFirstName, iLocationID, cBusPhone, iSchoolID, mEmail, mCellphone)
select
isnull(cLastname,'') as cLastname,
isnull(cFirstname,'') as cFirstname,
@v_p_iLocationID,
isnull(cHomePhone,'') as cBusPhone,
@iSchoolID as iSchoolID,
isnull(mEmail,'') as mEmail,
isnull(mCellPhone,'') as mCellPhone
from #contact
where #contact.iIndex = @v_t_iIndex
and #contact.StudentID = @v_t_StudentID
--grab last inserted icontactid
set @v_p_iContactID = (SELECT SCOPE_IDENTITY())
insert into palliser.dbo.UserContact (iContactID, iSchoolID) values(@v_p_iContactID, @iSchoolID)
insert into palliser.dbo.ContactRelation (iContactID, iStudentID, iLV_RelationID, iContactPriority, lLivesWithStudent, lMail, iSchoolID, mComment)
select
@v_p_iContactID as iContactID,
@v_p_iStudentID as iStudentID,
isnull(@v_p_iLV_RelationID,0) as iLV_RelationID,
isnull(iContactPriority,0) as iContactPriority,
0 as lLivesWithStudent,
isnull(lMailTo,0) as lMail,
@iSchoolID as iSchoolID,
ISNULL(mComment,0) as mComment
from #contactrelation
join #contact
on #contact.ParentID = #contactrelation.ParentID
and #contact.iIndex = @v_t_iIndex
where #contactrelation.StudentID = @v_t_StudentID
fetch next from cContact into @v_t_iIndex
end
close cContact
deallocate cContact
fetch next from cStudent into @v_t_studentIndex
end
close cStudent
deallocate cStudent
-------------------------------------------------------------------------------------------------
--USER DEFINED FIELDS----------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
declare @udf_area int
declare @udf_field int
declare @udf_type int
declare @udf_caption varchar(50)
declare @i int
declare @previ int
declare @udf_studentIndex int
--create an area to put these fields in
insert into palliser.dbo.areas (cname, iareatype, lsystemarea, linternal) values('UDFields',11,0,0)
set @udf_area = (select top 1 iareasid from palliser.dbo.areas where cname like 'UDFields')
drop table #udfields
create table #udfields (
FieldID int,
cCaption varchar(50),
FieldType int,
mComment varchar(100)
)
insert into #udfields
select distinct FieldID, left(FieldName,25), FieldType, FieldName
from udfield
--iterate through each field and add datatype and lookups if required
declare cField cursor for
select FieldID, cCaption, FieldType from #udfields
open cField
fetch cField into @udf_field, @udf_caption, @udf_type
while @@fetch_status=0
begin
--get next available fieldsid
set @i = 0
set @previ = 0
declare cNextField cursor for
select iFieldsID from palliser.dbo.Fields
open cNextField
fetch cNextField into @i
while @@fetch_status=0
begin
if ((@i - @previ) > 1)
break
set @previ = @i
fetch next from cNextField into @i
end
close cNextField
deallocate cNextField
--need to turn this into a yes/no field. match first letter.
if(@udf_type = 2)
begin
--need to create a field linked to a (schoollogic) datatype of 3 (yesno)
insert into palliser.dbo.Fields(
iFieldsID,
iDatatypesID,
iAreasID,
cCaption,
cSupportCaption,
cAlias,
cFieldName,
lRequired,
iWidth,
iDecimal,
cFormat,
cStatusBarText,
iValidationsID,
iDefaultValueTypesID,
iDefaultFunctionID,
mDefaultValue,
mComment,
lSystemField,
lStaticCaption,
lStaticDefaultValue,
lUseDefaultValue,
lStaticFormat,
lUseFormat,
lStaticValidation,
lInternal,
lCascade,
lSearchable,
lRemoveLink,
lCalculated,
cDisplayExpr,
cTagName,
cTagFieldName,
lAudit,
iOrder,
iTabOrder,
lSped,
lOffCampus,
cUDRExpr)
select
(@previ + 1),
3,
@udf_area,
@udf_caption,
@udf_caption,
'USERSTUDENT',
('UF_' + cast((@previ + 1) as char)),
0,
1,
0,
'',
'',
0,
0,
0,
'',
@udf_caption,
0,
'',
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
'',
'',
0,
0,
0,
0,
0,
0
end
else
if(@udf_type in (4,6)) --lookup value, going to convert as text right now, may fix later depending on time
begin
--need to create a field linked to a (schoollogic) datatype of 2 (char)
insert into palliser.dbo.Fields(
iFieldsID,
iDatatypesID,
iAreasID,
cCaption,
cSupportCaption,
cAlias,
cFieldName,
lRequired,
iWidth,
iDecimal,
cFormat,
cStatusBarText,
iValidationsID,
iDefaultValueTypesID,
iDefaultFunctionID,
mDefaultValue,
mComment,
lSystemField,
lStaticCaption,
lStaticDefaultValue,
lUseDefaultValue,
lStaticFormat,
lUseFormat,
lStaticValidation,
lInternal,
lCascade,
lSearchable,
lRemoveLink,
lCalculated,
cDisplayExpr,
cTagName,
cTagFieldName,
lAudit,
iOrder,
iTabOrder,
lSped,
lOffCampus,
cUDRExpr)
select
(@previ + 1),
2,
@udf_area,
@udf_caption,
@udf_caption,
'USERSTUDENT',
('UF_' + cast((@previ + 1) as char)),
0,
1,
0,
'',
'',
0,
0,
0,
'',
@udf_caption,
0,
'',
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
'',
'',
'',
0,
0,
0,
0,
0,
0
end
fetch next from cField into @udf_field, @udf_caption, @udf_type
end
close cField
deallocate cField
COMMIT TRANSACTION
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--------STOP HERE AND RUN THE REBUILD FIELDS-----------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--POPULATE USER DEFINED FIELDS NOW------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
--select * from palliser.dbo.fields where cAlias like 'USERSTUDENT'
-- --need to fill values in the field
-- declare cStudent cursor for
-- select iIndex from #student
-- open cStudent
-- fetch cStudent into @udf_studentIndex
-- while @@fetch_status=0
-- begin
-- --select UDD.Data from #student s
-- --join UDData udd
-- -- on udd.ID = s.studentid
-- --where s.iIndex = @udf_studentIndex
-- --and udd.FieldID = @udf_field
-- fetch next from cStudent into @udf_studentIndex
-- end
-- close cStudent
-- deallocate cStudent
-------------------------------------------------------------------------------------------------
--ROSTER LOOP------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
drop table #roster
create table #roster (
iIndex int identity,
iStudentID int,
iClassID int,
dInDate smalldatetime,
dOutDate smalldatetime,
CompletionStatus varchar(50),
iSchoolID int,
iRelated_CourseID int,
cLevel varchar(20),
iMarksHistoryID int,
FinalGradeAvg int,
mComment varchar(max))
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
insert into #roster (
iStudentID,
iClassID,
dInDate,
dOutDate,
CompletionStatus,
iSchoolID,
iRelated_CourseID,
cLevel,
iMarksHistoryID,
FinalGradeAvg,
mComment)
select
s.istudentid,
c.iclassid,
(select dStartDate from palliser.dbo.Track where iTrackID = 1),
(select dEndDate from palliser.dbo.Track where iTrackID = 1),
'',
1,
0,
0,
0,
FinalGradeAvg,
Com1
from palliser.dbo.Student s
join Roster r
on r.studentid = s.cstudentnumber
join #classes c
on c.classid = r.ClassID
--insert enrollment part of roster
insert into palliser.dbo.enrollment (
iStudentID,
iClassID,
dInDate,
dOutDate,
iLV_CompletionStatusID,
iSchoolID)
select
iStudentID,
iClassID,
dInDate,
'',
0,
1
from #roster
--insert marks part of roster
insert into palliser.dbo.Marks (
iReportPeriodID,
iEnrollmentiD,
iStudentID,
iClassID,
cFinalMark,
mComment,
iSchoolID)
select
1,
e.iEnrollmentID,
e.istudentid,
e.iclassid,
isnull(r.FinalGradeAvg,0),
isnull(r.mComment,''),
1
from #roster r
join palliser.dbo.enrollment e
on r.iStudentID = e.istudentid and r.iClassID = e.iclassid
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--attempt to make classschedule record lol
create table #classschedule(
iclassid int, --sl
classid int, --renweb
day int, --equivalent to 'col' in renweb schedulepatternstimetable table
block int, --equivalent to 'row' in renweb schedulepatternstimetable table
term int
)
insert into #classschedule (
iclassid,
classid,
day,
block,
term)
select
ct.iClassID,
c.ClassID,
stp.Col,
stp.row,
1
from schedulepatternstimetable stp
join classes c
on c.Pattern = stp.PatternNumber
join ScheduleTemplate st
on st.TemplateID = stp.TemplateID
join #classes ct
on ct.ClassID = c.ClassID
where st.YearID = 257
and c.Term1 = 1
order by classid
insert into #classschedule (
iclassid,
classid,
day,
block,
term)
select
ct.iClassID,
c.ClassID,
stp.Col,
stp.row,
2
from schedulepatternstimetable stp
join classes c
on c.Pattern = stp.PatternNumber
join ScheduleTemplate st
on st.TemplateID = stp.TemplateID
join #classes ct
on ct.ClassID = c.ClassID
where st.YearID = 257
and c.Term2 = 1
order by classid
insert into #classschedule (
iclassid,
classid,
day,
block,
term)
select
ct.iClassID,
c.ClassID,
stp.Col,
stp.row,
3
from schedulepatternstimetable stp
join classes c
on c.Pattern = stp.PatternNumber
join ScheduleTemplate st
on st.TemplateID = stp.TemplateID
join #classes ct
on ct.ClassID = c.ClassID
where st.YearID = 257
and c.Term3 = 1
order by classid
insert into #classschedule (
iclassid,
classid,
day,
block,
term)
select
ct.iClassID,
c.ClassID,
stp.Col,
stp.row,
4
from schedulepatternstimetable stp
join classes c
on c.Pattern = stp.PatternNumber
join ScheduleTemplate st
on st.TemplateID = stp.TemplateID
join #classes ct
on ct.ClassID = c.ClassID
where st.YearID = 257
and c.Term1 = 4
order by classid
insert into palliser.dbo.ClassSchedule(
select * from palliser.dbo.classschedule
COMMIT TRANSACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------
--iterate through each student and add their courses to enrollment
declare cStudent cursor for
select iIndex from #student
open cStudent
fetch cStudent into @v_t_studentIndex
while @@fetch_status=0
begin
fetch next from cStudent into @v_t_studentIndex
end
close cStudent
deallocate cStudent
--select * from palliser.dbo.student
--where cstudentnumber in(
-- select cstudentnumber from palliser.dbo.student
-- group by cstudentnumber, ischoolid
-- having count(cstudentnumber) > 1)
--order by cstudentnumber
@othtim

othtim commented Mar 31, 2014

Copy link
Copy Markdown
Author

For reference, never going to be used again

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment