conversion scripts for Sino

sino_conversion.sql

 

begin transaction

delete from contact

--cstreet is too small
alter table location alter column cstreet varchar(70)
 
 
insert into Country (
	cName, 
	cCode, 
	cRegionCaption,
	cPostalCodeCaption,
	cPostalCodeFormat,
	cPhoneFormat,
	iSchoolID)
VALUES(
	'Korea',
	'Korea',
	'Province',
	'Postal Code',
	'XXXXXXXXXXXXXXXXXXXXXX',
	'XXXXXXXXXXXXXXXXXXXXXX',
	1)
	
	
--insert relationship we will use in contactrelation
insert into lookupvalues(
	cName, 
	cCode,
	iDataTypesID,
	iSchoolID)
VALUES(
	'Contact',
	'Contact',
	41,
	1
)

-----------------------------------------------
	
	
declare @studentid int
declare @contactid int
declare @locationid int

declare @UF_63 varchar(max)
declare @temp varchar(1000)
declare @postalcode varchar(10)
declare @country varchar(10)
declare @cHouseNo varchar(100)
declare @city varchar(100)
declare @cstreet varchar(255)

declare c cursor for
	select s.istudentid from Student s
	join UserStudent us
		on s.istudentid = us.istudentid
	where UF_63 != ''
	
	
open c
fetch c into @studentid
while @@FETCH_STATUS=0
begin

	insert into Contact (
		cLastName, 
		cFirstName,  ---this will be a placeholder, we will update later
		iLocationID, --also placeholder, we will update later
		cBusPhone,	
		iSchoolID,
		mEmail)
	select 
		left(us.UF_63, (select CHARINDEX(' ', us.UF_63,1))),
		'placeholder', --firstname
		'1234',
		left(us.UF_64,25),
		1,
		''
	from Student s
	join userstudent us 
		on s.iStudentID = us.iStudentID 
	where s.iStudentID = @studentid
	
	
	
	--grab last contactid
	set @contactid = SCOPE_IDENTITY()
	
	
	--insert contactrelation
	insert into ContactRelation(
		iContactID,
		iStudentID,
		iLV_RelationID,
		iContactPriority,
		lLivesWithStudent,
		lMail,
		iSchoolID)
	VALUES(
		@contactid,
		@studentid,
		(select ilookupvaluesid from lookupvalues where cName like 'Contact' and iDataTypesID = 41),
		1,
		0,
		1,
		1)
	
	
	set @UF_63 = (select UF_63
	from Student s
	join userstudent us 
		on s.iStudentID = us.iStudentID 
	where s.iStudentID = @studentid)
	
	
		
	--trim off the last name
	update UserStudent
	set UF_63 = RIGHT( UF_63, len(UF_63) - CHARINDEX(' ', UF_63,1) )
	from Student s
	join userstudent us 
		on s.iStudentID = us.iStudentID 
	where s.iStudentID = @studentid

		
	
	--update first name
	update Contact 
	set cFirstName = left(left(us.UF_63, CHARINDEX(',', us.UF_63,1) - 1),25)
	from Student s
	join userstudent us 
		on s.iStudentID = us.iStudentID 
	where s.iStudentID = @studentid
	and icontactid = @contactid



	--trim off the first name
	update UserStudent
	set UF_63 = RIGHT( UF_63, len(UF_63) - CHARINDEX(',', UF_63,1) )
	from Student s
	join userstudent us 
		on s.iStudentID = us.iStudentID 
	where s.iStudentID = @studentid



	--misc
	if(	(select CHARINDEX('China', UF_63, 1) from Student s
		 join UserStudent us
		 on s.istudentid = us.istudentid
		 where s.iStudentID = @studentid) > 0) 
		BEGIN
			set @temp = LEFT((select us.UF_63 from Student s join UserStudent us on s.istudentid = us.istudentid where s.iStudentID = @studentid),
							 (select CHARINDEX('China', us.UF_63, 1) from Student s join UserStudent us on s.istudentid = us.istudentid where s.iStudentID = @studentid) - 2)
			--select @temp --debug
			
			
			
			set @postalcode = (select isnull(right(@temp, (select CHARINDEX(',', REVERSE(@temp), 1)) - 1),''))
			set @country = 'CHINA'
			
			
			
			--trim off the country and postalcode
			update UserStudent
			set UF_63 = LEFT(UF_63, CHARINDEX(@postalcode, UF_63, 1) - 2)
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid
			
			
			
			--update cHouseNo
			set @cHouseNo = 
			(select isnull(left(left(us.UF_63, CHARINDEX(',', us.UF_63,1) - 1),25),'')
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)



			--trim off the @cHouseNo
			update UserStudent
			set UF_63 = RIGHT( UF_63, len(UF_63) - CHARINDEX(',', UF_63,1) )
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid

			
			
			--grab @city
			set @city = (select isnull(LEFT( REVERSE(UF_63) , CHARINDEX(',', REVERSE(UF_63), 1) - 1), '')
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)
			set @city = REVERSE(@city)
			


			--grab @cstreet
			update UserStudent 
			set @temp = (select LEFT(UF_63, (LEN(UF_63) - CHARINDEX( ',', REVERSE(UF_63), 1)))			
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)
			set @cstreet = @temp
			
			
		
			--debug
			select @cHouseNo, @cstreet, @city, @postalcode, @country, UF_63 from UserStudent where iStudentID = @studentid			
			
			
			--check if the lookupvalue already exists, otherwise insert it
			if( (select top 1 ilookupvaluesid from LookupValues 
				 where cName like @city 
				 and iDataTypesID = (select iDataTypesID from Datatypes where cName like 'City'))  like '')
				begin
					select 'hi'
				end
			else
				begin
					insert into LookupValues(
						cName, 
						cCode, 
						iDataTypesID, 
						iSchoolID)
					VALUES(
						@city,
						@city,
						37,
						1)
				end
				
							
			--insert location
			insert into Location (
				cHouseNo, 
				cStreet,
				iLV_CityID,
				iLV_RegionID,
				iCountryID,
				cPostalCode,
				cPhone,
				iSchoolID)
			VALUES (
				@cHouseNo,
				left(@cStreet, 70),
				(select top 1 ilookupvaluesid from LookupValues where cName like @city and iDataTypesID = 37), --@city,
				0,
				1,
				@postalcode,
				'',
				1)
				
				
				
			--grab last location
			set @locationid = SCOPE_IDENTITY()
			
				
			update Contact set iLocationID = @locationid where iContactID = @contactid 
			
		
			
		END
	else
		BEGIN
		
			if(isnull((select CHARINDEX('Korea', us.UF_63, 1) from Student s join UserStudent us on s.istudentid = us.istudentid where s.iStudentID = @studentid),0) > 0)
					set @temp = isnull(LEFT((select us.UF_63 from Student s join UserStudent us on s.istudentid = us.istudentid where s.iStudentID = @studentid),
							 isnull((select CHARINDEX('Korea', us.UF_63, 1) from Student s join UserStudent us on s.istudentid = us.istudentid where s.iStudentID = @studentid),0) - 2), '')
			else
					set @temp = 0
			--select @temp --debug			
			
			
			
			set @postalcode = (select isnull(right(@temp, (select CHARINDEX(',', REVERSE(@temp), 1)) - 1), ''))
			set @country = 'KOREA'
			
			
			
			--trim off the country and postalcode
			update UserStudent
			set UF_63 = LEFT(UF_63, CHARINDEX(@postalcode, UF_63, 1) - 2)
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid
			
			
			
			--update cHouseNo
			set @cHouseNo = 
			(select left(left(us.UF_63, CHARINDEX(',', us.UF_63,1) - 1),25)
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)



			--trim off the @cHouseNo
			update UserStudent
			set UF_63 = RIGHT( UF_63, len(UF_63) - CHARINDEX(',', UF_63,1) )
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid

			
			
			--grab @city
			set @city = (select isnull(LEFT( REVERSE(UF_63) , CHARINDEX(',', REVERSE(UF_63), 1) - 1), '')
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)
			set @city = isnull(REVERSE(@city), '')
			


			--grab @cstreet
			update UserStudent 
			set @temp = (select LEFT(UF_63, (LEN(UF_63) - CHARINDEX( ',', REVERSE(UF_63), 1)))			
			from Student s
			join userstudent us 
				on s.iStudentID = us.iStudentID 
			where s.iStudentID = @studentid)
			set @cstreet = @temp
			
			
		
			--debug
			--select @cHouseNo, @cstreet, @city, @postalcode, @country, UF_63 from UserStudent where iStudentID = @studentid
			
			
			--check if the lookupvalue already exists, otherwise insert it
			if( (select ilookupvaluesid from LookupValues 
				 where cName like @city 
				 and iDataTypesID = (select iDataTypesID from Datatypes where cName like 'City'))  like '')
				begin
					select 'hi'
				end
			else
				begin
					insert into LookupValues(
						cName, 
						cCode, 
						iDataTypesID, 
						iSchoolID)
					VALUES(
						@city,
						@city,
						37,
						1)
				end
				
							
			--insert location
			insert into Location (
				cHouseNo, 
				cStreet,
				iLV_CityID,
				iLV_RegionID,
				iCountryID,
				cPostalCode,
				cPhone,
				iSchoolID)
			VALUES (
				@cHouseNo,
				left(@cStreet, 70),
				(select ilookupvaluesid from LookupValues where cName like @city and iDataTypesID = 37), --@city,
				0,
				3,
				@postalcode,
				'',
				1)
				
				
				
			--grab last location
			set @locationid = SCOPE_IDENTITY()
			
				
			update Contact set iLocationID = @locationid where iContactID = @contactid 
			
		END

	
	update userstudent
	set UF_63 = @UF_63
	where istudentid = @studentid 

fetch next from c into @studentid
end
close c
deallocate c	


	--select * from Student s
	--join UserStudent us
	--	on s.istudentid = us.istudentid
	--where UF_63 != ''
	
		
	--select * from contact


	
	--(select CHARINDEX('China', UF_63, 1) from Student s
	--join UserStudent us
	--	on s.istudentid = us.istudentid
	--where s.iStudentID = @studentid)



select * from contact
join ContactRelation
	on contact.iContactID = contactrelation.iContactID 
join location 
	on location.iLocationID = contact.ilocationid

	


commit transaction
--rollback transaction

sino_conversion_markshistory.sql

select * from legenddetails
select mh.cAlphaMark, * from markshistory mh



declare @i int
declare @finalmark int


declare c cursor for 
	select imarkshistoryid from markshistory
	
open c
fetch next from c into @i
WHILE @@FETCH_STATUS = 0
begin

	set @finalmark = (select nFinalMark from MarksHistory where iMarksHistoryID = @i)
	
	if( @finalmark >= 86 )
	update markshistory set cAlphaMark = 'A' where iMarksHistoryID = @i
	else
			if( @finalmark >= 73 )
			update markshistory set cAlphaMark = 'B' where iMarksHistoryID = @i
			else
					if( @finalmark >= 67 )
					update markshistory set cAlphaMark = 'C+' where iMarksHistoryID = @i
					else
							if( @finalmark >= 60 )
							update markshistory set cAlphaMark = 'C' where iMarksHistoryID = @i
							else
									if( @finalmark >=50 )
									update markshistory set cAlphaMark = 'C-' where iMarksHistoryID = @i
									else
											if( @finalmark >= 0 )
											update markshistory set cAlphaMark = 'F' where iMarksHistoryID = @i
	

fetch next from c into @i
end
close c
deallocate c