129 lines
7.9 KiB
C#
129 lines
7.9 KiB
C#
|
using Microsoft.EntityFrameworkCore;
|
|||
|
|
|
|||
|
|
namespace CoreDatabase.Scripts
|
|||
|
|
{
|
|||
|
|
/// <summary>
|
|||
|
|
/// Скрипты для миграции данных по кафедре
|
|||
|
|
/// </summary>
|
|||
|
|
public static class DepartmentMigrationScript
|
|||
|
|
{
|
|||
|
|
private static readonly string clearMigration =
|
|||
|
|
@"DELETE FROM [DepartmentDatabasePortal].[dbo].[Classrooms];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[DisciplineBlocks];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[Disciplines];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[Posts];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[Lecturers];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[TimeNorms];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlans];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlanRecords];
|
|||
|
|
GO
|
|||
|
|
DELETE FROM [DepartmentDatabasePortal].[dbo].[AcademicPlanRecordTimeNormHours];
|
|||
|
|
GO";
|
|||
|
|
|
|||
|
|
private static readonly string classroomsMigration =
|
|||
|
|
@"DECLARE @employeeId uniqueidentifier;
|
|||
|
|
|
|||
|
|
SELECT @employeeId = emp.[Id] FROM [DepartmentDatabasePortal].[dbo].[Employees] emp
|
|||
|
|
|
|||
|
|
INSERT INTO [DepartmentDatabasePortal].[dbo].[Classrooms]([Id],[Number],[EmployeeId],[ClassroomType],[Square],[Capacity],[HaveProjector],[SecurityCode],
|
|||
|
|
[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[Number],@employeeId,[ClassroomType],1,[Capacity],0,'-',[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[Classrooms]";
|
|||
|
|
|
|||
|
|
private static readonly string disciplineBlockMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[DisciplineBlocks]([Id],[Title],[DisciplineBlockUseForGrouping],[DisciplineBlockOrder],
|
|||
|
|
[DisciplineBlockBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[Title],[DisciplineBlockUseForGrouping],[DisciplineBlockOrder],[DisciplineBlockBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted] FROM
|
|||
|
|
[DepartmentDatabaseContext].[dbo].[DisciplineBlocks]";
|
|||
|
|
|
|||
|
|
private static readonly string disciplineMigration =
|
|||
|
|
@"DELETE FROM [DepartmentDatabaseContext].[dbo].[Disciplines] WHERE [Id]='6CC684D5-900C-4278-AA43-0B83D0711C55' OR [Id]='A6598227-8449-4884-8290-13EABEC26924'
|
|||
|
|
OR [Id]='4581ADC8-5A15-4F95-A40D-1607856ACB5C' OR [Id]='9DA4D810-025A-4A03-81FD-293DEF52FA4B' -- дублт в дисципдинах
|
|||
|
|
|
|||
|
|
INSERT INTO [DepartmentDatabasePortal].[dbo].[Disciplines]([Id],[DisciplineBlockId],[DisciplineName],[DisciplineShortName],[Description],
|
|||
|
|
[DisciplineBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[DisciplineBlockId],[DisciplineName],[DisciplineShortName],[DisciplineDescription],[DisciplineBlueAsteriskName],[DateCreate],[DateDelete],[IsDeleted]
|
|||
|
|
FROM [DepartmentDatabaseContext].[dbo].[Disciplines]";
|
|||
|
|
|
|||
|
|
private static readonly string postMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[Posts]([Id],[PostName],[Hours],[Order],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[StudyPostTitle],[Hours],1,[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[LecturerStudyPosts]";
|
|||
|
|
|
|||
|
|
private static readonly string lecturerMigration =
|
|||
|
|
@"DECLARE @userId uniqueidentifier;
|
|||
|
|
|
|||
|
|
SELECT @userId = u.[Id] FROM [DepartmentDatabasePortal].[dbo].[Users] u WHERE u.[UserName]='admin'
|
|||
|
|
|
|||
|
|
INSERT INTO [DepartmentDatabasePortal].[dbo].[Lecturers]([Id],[UserId],[LastName],[FirstName],[Patronymic],[Abbreviation],[DateBirth],[Address],[Email],
|
|||
|
|
[MobileNumber],[HomeNumber],[Description],[Photo],[GroupElectricalSafety],[OnlyForPrivate],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],@userId,[LastName],[FirstName],[Patronymic],[Abbreviation],[DateBirth],[Address],[Email],[MobileNumber],[HomeNumber],[Description],[Photo],'I',
|
|||
|
|
[OnlyForPrivate],[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[Lecturers]";
|
|||
|
|
|
|||
|
|
private static readonly string educationdirectionrMigration =
|
|||
|
|
@"DECLARE @lecturerId uniqueidentifier;
|
|||
|
|
|
|||
|
|
SELECT @lecturerId = l.[Id] FROM [DepartmentDatabasePortal].[dbo].[Lecturers] l
|
|||
|
|
|
|||
|
|
INSERT INTO [DepartmentDatabasePortal].[dbo].[EducationDirections]([Id],[Cipher],[ShortName],[Title],[Profile],[Qualification],[LecturerId],[Description],
|
|||
|
|
[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[Cipher],[ShortName],[Title],[Profile],[Qualification],@lecturerId,[Description],[DateCreate],[DateDelete],[IsDeleted] FROM
|
|||
|
|
[DepartmentDatabaseContext].[dbo].[EducationDirections]";
|
|||
|
|
|
|||
|
|
private static readonly string timeNormMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[TimeNorms]([Id],[DisciplineBlockId],[TimeNormName],[TimeNormShortName]
|
|||
|
|
,[TimeNormOrder],[TimeNormEducationDirectionQualification],[KindOfLoadName],[KindOfLoadAttributeName],[KindOfLoadBlueAsteriskName]
|
|||
|
|
,[KindOfLoadBlueAsteriskAttributeName],[KindOfLoadBlueAsteriskPracticName],[UseInLearningProgress],[UseInSite],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[DisciplineBlockId],[TimeNormName],[TimeNormShortName],[TimeNormOrder],[TimeNormEducationDirectionQualification],[KindOfLoadName]
|
|||
|
|
,[KindOfLoadAttributeName],[KindOfLoadBlueAsteriskName],[KindOfLoadBlueAsteriskAttributeName],[KindOfLoadBlueAsteriskPracticName]
|
|||
|
|
,[UseInLearningProgress],[UseInSite],[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[TimeNorms] WHERE
|
|||
|
|
[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";
|
|||
|
|
|
|||
|
|
private static readonly string academicplanMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlans]([Id],[EducationDirectionId],[YearEntrance],[YearFinish],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT [Id],[EducationDirectionId],2020 + ABS(CHECKSUM(NEWID()) % 100),2024,[DateCreate],[DateDelete],[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[AcademicPlans] WHERE
|
|||
|
|
[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";
|
|||
|
|
|
|||
|
|
private static readonly string academicplanrecordsMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlanRecords]([Id],[AcademicPlanId],[DisciplineId],[InDepartment],[Semester],[Zet]
|
|||
|
|
,[AcademicPlanRecordParentId],[IsParent],[IsFacultative],[DateCreate],[DateDelete],[IsDeleted])
|
|||
|
|
SELECT apr.[Id],apr.[AcademicPlanId],apr.[DisciplineId],apr.[InDepartment],apr.[Semester],apr.[Zet],apr.[AcademicPlanRecordParentId],apr.[IsParent],apr.[IsFacultative]
|
|||
|
|
,apr.[DateCreate],apr.[DateDelete],apr.[IsDeleted] FROM [DepartmentDatabaseContext].[dbo].[AcademicPlanRecords] apr
|
|||
|
|
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlans] ap on apr.[AcademicPlanId]=ap.[Id] WHERE
|
|||
|
|
ap.[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";
|
|||
|
|
|
|||
|
|
private static readonly string academicplanrecordhoursMigration =
|
|||
|
|
@"INSERT INTO [DepartmentDatabasePortal].[dbo].[AcademicPlanRecordTimeNormHours]([Id],[AcademicPlanRecordId],[TimeNormId],[PlanHours],[DateCreate],[DateDelete],
|
|||
|
|
[IsDeleted])
|
|||
|
|
SELECT apre.[Id],apre.[AcademicPlanRecordId],apre.[TimeNormId],apre.[PlanHours],apre.[DateCreate],apre.[DateDelete],apre.[IsDeleted]
|
|||
|
|
FROM [DepartmentDatabaseContext].[dbo].[AcademicPlanRecordElements] apre
|
|||
|
|
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlanRecords] apr on apre.[AcademicPlanRecordId]=apr.[Id]
|
|||
|
|
JOIN [DepartmentDatabaseContext].[dbo].[AcademicPlans] ap on apr.[AcademicPlanId]=ap.[Id] WHERE
|
|||
|
|
ap.[AcademicYearId]='4FFC16B7-C1A5-47B1-B780-821ED70793DC'";
|
|||
|
|
|
|||
|
|
/// <summary>
|
|||
|
|
/// Перенос данных по безопасности
|
|||
|
|
/// </summary>
|
|||
|
|
/// <param name="context"></param>
|
|||
|
|
public static void RunScript(DbContext context)
|
|||
|
|
{
|
|||
|
|
context.Database.ExecuteSqlRaw(clearMigration, null);
|
|||
|
|
|
|||
|
|
context.Database.ExecuteSqlRaw(classroomsMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(disciplineBlockMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(disciplineMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(postMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(lecturerMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(educationdirectionrMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(timeNormMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(academicplanMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(academicplanrecordsMigration, null);
|
|||
|
|
context.Database.ExecuteSqlRaw(academicplanrecordhoursMigration, null);
|
|||
|
|
}
|
|||
|
|
}
|
|||
|
|
}
|