----------------------------------------- Ahmed Khled 2025-12-09 ----------------- INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1210,N'Employees',N'الموظفين',27,N'/Personal/Employees',N'Index',N'fas fa-users',N'get',1,1,1) GO INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1210,22) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4300,N'الموظفين',N'Employees',1210,1) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4301,N'عرض الموظفين',N'View Employees',1210,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4302,N'إضافة موظف',N'Add Employee',1210,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4303,N'تعديل موظف',N'Edit Employee',1210,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4304,N'حذف موظف',N'Delete Employee',1210,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4305,N'حفظ موظف',N'Save Employee',1210,2) GO ----------------------------------------- End 2025-12-09 ----------------- ----------------------------------------- Mostafa 2025-12-09 ----------------- /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.InsuranceTypes'); -- Drop FK inside this table (outgoing) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.InsuranceTypes'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.InsuranceTypes') AND type IN('PK','UQ','C','D'); EXEC(@s); IF OBJECT_ID(N'dbo.Tmp_InsuranceTypes', 'U') IS NOT NULL DROP TABLE dbo.Tmp_InsuranceTypes; CREATE TABLE dbo.Tmp_InsuranceTypes( InsuranceCode tinyint NOT NULL IDENTITY (0, 1), Name nvarchar(150), Arabic_Name nvarchar(150), BE float(53) NULL, BC float(53) NULL, BMAX float(53) NULL, VE float(53) NULL, VC float(53) NULL, VMAX float(53) NULL, BEOpen bit NULL, BCOpen bit NULL, BMAXOpen bit NULL, VEOpen bit NULL, VCOpen bit NULL, VMAXOpen bit NULL, PercentOfSC float(53) NULL, Area nvarchar(100) NULL, Office nvarchar(100) NULL, CompanyName nvarchar(100) NULL, HrManager nvarchar(100) NULL, LowStatus nvarchar(100) NULL, BuildingNumber nvarchar(50) NULL, StreetName nvarchar(100) NULL, Village nvarchar(100) NULL, Section nvarchar(100) NULL, City nvarchar(100) NULL, Insurnumber nvarchar(100) NULL, MyDate datetime NULL, AsBe nvarchar(100) NULL, Confess nvarchar(100) NULL, MinimumDaysToCalculateInCaseOfResignation tinyint NULL, MedicalBasicEmployee float(53) NULL, MedicalBasicCompany float(53) NULL, MedicalVariableEmployee float(53) NULL, MedicalVariableCompany float(53) NULL, PercentOfPool float(53) NULL, InsuranceForm bit NULL, MinimumBasic float(53) NULL, MinimumTotalInsurance float(53) NULL, MandatoryNationality tinyint NULL, SupportEmployee float(53) NULL, SupportCompany float(53) NULL, PensionEmployee float(53) NULL, PensionCompany float(53) NULL, RiskEmployee float(53) NULL, RiskCompany float(53) NULL ) ON [PRIMARY] ALTER TABLE dbo.Tmp_InsuranceTypes SET (LOCK_ESCALATION = TABLE) ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BE DEFAULT ((0)) FOR BE ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BC DEFAULT ((0)) FOR BC ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BMAX DEFAULT ((0)) FOR BMAX ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VE DEFAULT ((0)) FOR VE ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VC DEFAULT ((0)) FOR VC ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VMAX DEFAULT ((0)) FOR VMAX ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BEOpen DEFAULT ((0)) FOR BEOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BCOpen DEFAULT ((0)) FOR BCOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_BMAXOpen DEFAULT ((0)) FOR BMAXOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VEOpen DEFAULT ((0)) FOR VEOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VCOpen DEFAULT ((0)) FOR VCOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_VMAXOpen DEFAULT ((0)) FOR VMAXOpen ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_PercentOfSC_IT DEFAULT ((0)) FOR PercentOfSC ALTER TABLE dbo.Tmp_InsuranceTypes ADD CONSTRAINT DF_InsuranceTypes_InsuranceForm DEFAULT ((0)) FOR InsuranceForm SET IDENTITY_INSERT dbo.Tmp_InsuranceTypes ON IF EXISTS(SELECT * FROM dbo.InsuranceTypes) EXEC('INSERT INTO dbo.Tmp_InsuranceTypes (InsuranceCode, Name, Arabic_Name, BE, BC, BMAX, VE, VC, VMAX, BEOpen, BCOpen, BMAXOpen, VEOpen, VCOpen, VMAXOpen, PercentOfSC, Area, Office, CompanyName, HrManager, LowStatus, BuildingNumber, StreetName, Village, Section, City, Insurnumber, MyDate, AsBe, Confess, MinimumDaysToCalculateInCaseOfResignation, MedicalBasicEmployee, MedicalBasicCompany, MedicalVariableEmployee, MedicalVariableCompany, PercentOfPool, InsuranceForm, MinimumBasic, MinimumTotalInsurance, MandatoryNationality, SupportEmployee, SupportCompany, PensionEmployee, PensionCompany, RiskEmployee, RiskCompany) SELECT InsuranceCode, Name, Arabic_Name, BE, BC, BMAX, VE, VC, VMAX, BEOpen, BCOpen, BMAXOpen, VEOpen, VCOpen, VMAXOpen, PercentOfSC, Area, Office, CompanyName, HrManager, LowStatus, BuildingNumber, StreetName, Village, Section, City, Insurnumber, MyDate, AsBe, Confess, MinimumDaysToCalculateInCaseOfResignation, MedicalBasicEmployee, MedicalBasicCompany, MedicalVariableEmployee, MedicalVariableCompany, PercentOfPool, InsuranceForm, MinimumBasic, MinimumTotalInsurance, MandatoryNationality, SupportEmployee, SupportCompany, PensionEmployee, PensionCompany, RiskEmployee, RiskCompany FROM dbo.InsuranceTypes WITH (HOLDLOCK TABLOCKX)') SET IDENTITY_INSERT dbo.Tmp_InsuranceTypes OFF DROP TABLE dbo.InsuranceTypes EXECUTE sp_rename N'dbo.Tmp_InsuranceTypes', N'InsuranceTypes', 'OBJECT' ALTER TABLE dbo.InsuranceTypes ADD CONSTRAINT PK_InsuranceTypes PRIMARY KEY CLUSTERED (InsuranceCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] COMMIT GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceLocal' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD InsuranceLocal TINYINT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='SCCalculateWay' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD SCCalculateWay TINYINT NULL CONSTRAINT DF_InsuranceTypes_SCCalculateWay DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MinimumDaysToCalculateInCaseOfNewHire' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD MinimumDaysToCalculateInCaseOfNewHire TINYINT NULL CONSTRAINT DF_InsuranceTypes_MinimumDaysToCalculateInCaseOfNewHire DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MaximumInsuranceBasicSalary' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD MaximumInsuranceBasicSalary FLOAT NULL CONSTRAINT DF_InsuranceTypes_MaximumInsuranceBasicSalary DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ExemptionPercentage' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD ExemptionPercentage FLOAT NULL CONSTRAINT DF_InsuranceTypes_ExemptionPercentage DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MaximumExemption' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD MaximumExemption FLOAT NULL CONSTRAINT DF_InsuranceTypes_MaximumExemption DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='MinimumInsuranceBasicSalary' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD MinimumInsuranceBasicSalary FLOAT NULL CONSTRAINT DF_InsuranceTypes_MinimumInsuranceBasicSalary DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceBasicSalaryFactor' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD InsuranceBasicSalaryFactor FLOAT NULL CONSTRAINT DF_InsuranceTypes_InsuranceBasicSalaryFactor DEFAULT ((0)); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ActivityStarted' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD ActivityStarted NVARCHAR(50) NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='TaxRegistrationNumber' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD TaxRegistrationNumber NVARCHAR(100) NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='AutomaticYearlyUpdatePercent' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD AutomaticYearlyUpdatePercent BIT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CalculationIfAppointmentResignationInSameMonth' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD CalculationIfAppointmentResignationInSameMonth BIT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='InsuranceStatus' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD InsuranceStatus NVARCHAR(10) NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_InsuranceTypes_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD CreatedBy INT NOT NULL CONSTRAINT DF_InsuranceTypes_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD Closed BIT NULL CONSTRAINT DF_InsuranceTypes_Closed DEFAULT (0); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD ModificationDate DATETIME NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Serial' AND Object_ID=Object_ID('InsuranceTypes')) BEGIN ALTER TABLE InsuranceTypes ADD Serial NVARCHAR(250) NULL; END GO IF EXISTS(SELECT 1 FROM InsuranceTypes WHERE Serial IS NULL) BEGIN UPDATE InsuranceTypes SET Serial = InsuranceCode WHERE Serial IS NULL; END GO SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON; SET NUMERIC_ROUNDABORT OFF; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; -- Set lock escalation for tables ALTER TABLE dbo.Nationality SET (LOCK_ESCALATION = TABLE); ALTER TABLE dbo.InsuranceTypes SET (LOCK_ESCALATION = TABLE); -- Add foreign key constraint ALTER TABLE dbo.InsuranceTypes ADD CONSTRAINT FK_InsuranceTypes_Nationality FOREIGN KEY (MandatoryNationality) REFERENCES dbo.Nationality (NationalityCode) ON UPDATE NO ACTION ON DELETE NO ACTION; go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1215,N'Basis',N'الاسس',1023,NULL,NULL,'fa-solid fa-database',NULL,1,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1215,22) Go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1216,N'Insurance Type',N'أنواع التأمين',1215,N'/Personal/InsuranceType',N'Index','fa-solid fa-hand-holding-medical',N'get',1,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1216,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4306,N'أنواع التأمين',N'Insurance Type',1216,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4307,N'عرض أنواع التأمين',N'View Insurance Type',1216,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4308,N'إضافة أنواع التأمين',N'Add Insurance Type',1216,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4309,N'تعديل أنواع التأمين',N'Edit Insurance Type',1216,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4310,N'حذف أنواع التأمين',N'Delete Insurance Type',1216,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4311,N'حفظ أنواع التأمين',N'Save Insurance Type',1216,2) Go ----------------------------------------- End 2025-12-09 ----------------- ----------------------------------------- Mostafa 2025-12-10 ----------------- BEGIN TRANSACTION; SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON; SET NUMERIC_ROUNDABORT OFF; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.Penalties'); -- Drop FK inside this table (outing) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.Penalties'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.objects WHERE parent_object_id = OBJECT_ID('dbo.Penalties') AND type IN('PK','UQ','C','D'); EXEC(@s); IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Colsed' AND Object_ID=Object_ID('Penalties')) BEGIN ALTER TABLE Penalties ADD [Colsed] BIT NULL DEFAULT ((0)); END GO -- Drop temp table if exists IF OBJECT_ID(N'dbo.Tmp_Penalties', 'U') IS NOT NULL DROP TABLE dbo.Tmp_Penalties; CREATE TABLE dbo.Tmp_Penalties ( EmployeePenaltyCode int NOT NULL IDENTITY (0, 1), Serial float(53) NOT NULL, EmployeeCode int NULL, PenaltyCode smallint NULL, [Once] tinyint NULL, Date datetime NULL, Amount float(53) NULL, Period tinyint NULL, PYear smallint NULL, EventDescription varchar(1000) NULL, Colsed bit NULL, Abate bit NULL, AddBy nvarchar(20) NULL, AddDate datetime NULL, LastChangeBy nvarchar(20) NULL, ChangeDate datetime NULL, MainType tinyint NULL, OriginalPYear int NULL, OriginalPeriod tinyint NULL, LinkSerial int NULL, Closed bit NULL, AmountValue float(53) NULL, AccApproved int NULL, DateHijri nvarchar(10) NULL, ExpirationDate datetime NULL, ExpirationDateHijri nvarchar(10) NULL, EventPlace nvarchar(255) NULL, EventTime nvarchar(5) NULL, MDT datetime NULL ) ON [PRIMARY] ALTER TABLE dbo.Tmp_Penalties SET (LOCK_ESCALATION = TABLE) ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Serial DEFAULT ((0)) FOR Serial ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_EmployeeCode DEFAULT ((0)) FOR EmployeeCode ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_PenaltyCode DEFAULT ((0)) FOR PenaltyCode ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Once DEFAULT ((0)) FOR [Once] ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Amount DEFAULT ((0)) FOR Amount ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Period DEFAULT ((0)) FOR Period ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_PYear DEFAULT ((0)) FOR PYear ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Abate DEFAULT ((0)) FOR Abate ALTER TABLE dbo.Tmp_Penalties ADD CONSTRAINT DF_Penalties_Closed DEFAULT ((0)) FOR Closed SET IDENTITY_INSERT dbo.Tmp_Penalties ON IF EXISTS(SELECT * FROM dbo.Penalties) EXEC('INSERT INTO dbo.Tmp_Penalties (EmployeePenaltyCode,Serial, EmployeeCode, PenaltyCode, [Once], Date, Amount, Period, PYear, EventDescription, Colsed, Abate, AddBy, AddDate, LastChangeBy, ChangeDate, MainType, OriginalPYear, OriginalPeriod, LinkSerial, Closed, AmountValue, AccApproved, DateHijri, ExpirationDate, ExpirationDateHijri, EventPlace, EventTime, MDT) SELECT TRY_CAST(Serial AS int),Serial, EmployeeCode, PenaltyCode, [Once], Date, Amount, Period, PYear, EventDescription, Colsed, Abate, AddBy, AddDate, LastChangeBy, ChangeDate, MainType, OriginalPYear, OriginalPeriod, LinkSerial, Closed, AmountValue, AccApproved, DateHijri, ExpirationDate, ExpirationDateHijri, EventPlace, EventTime, MDT FROM dbo.Penalties WITH (HOLDLOCK TABLOCKX)') SET IDENTITY_INSERT dbo.Tmp_Penalties OFF; DROP TABLE dbo.Penalties EXECUTE sp_rename N'dbo.Tmp_Penalties', N'Penalties', 'OBJECT' ALTER TABLE dbo.Penalties ADD CONSTRAINT PK_Penalties PRIMARY KEY CLUSTERED (EmployeePenaltyCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] COMMIT GO IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'AddBy' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN AddBy; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'AddDate' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN AddDate; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'LastChangeBy' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN LastChangeBy; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'ChangeDate' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN ChangeDate; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN MDT; END go IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('Penalties')) BEGIN ALTER TABLE Penalties ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_Penalties_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('Penalties')) BEGIN ALTER TABLE Penalties ADD CreatedBy INT NOT NULL CONSTRAINT DF_Penalties_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('Penalties')) BEGIN ALTER TABLE Penalties ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('Penalties')) BEGIN ALTER TABLE Penalties ADD ModificationDate DATETIME NULL; END GO DELETE FROM dbo.Penalties WHERE NOT EXISTS ( SELECT 1 FROM dbo.PenaltiesList pl WHERE pl.PenaltyCode = dbo.Penalties.PenaltyCode); go DELETE p FROM dbo.Penalties p WHERE NOT EXISTS ( SELECT 1 FROM dbo.Employees e WHERE e.EmployeeCode = p.EmployeeCode); go BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION ALTER TABLE dbo.PenaltiesList SET (LOCK_ESCALATION = TABLE) COMMIT BEGIN TRANSACTION ALTER TABLE dbo.Employees SET (LOCK_ESCALATION = TABLE) COMMIT BEGIN TRANSACTION ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_Employees FOREIGN KEY ( EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_PenaltiesList FOREIGN KEY(PenaltyCode) REFERENCES dbo.PenaltiesList(PenaltyCode) ON UPDATE NO ACTION ON DELETE NO ACTION ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_SecurityUserCreatedBy FOREIGN KEY (CreatedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE NO ACTION ON DELETE NO ACTION ALTER TABLE dbo.Penalties ADD CONSTRAINT FK_Penalties_SecurityUserModifiedBy FOREIGN KEY(ModifiedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE NO ACTION ON DELETE NO ACTION ALTER TABLE dbo.Penalties SET (LOCK_ESCALATION = TABLE) COMMIT GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='PenaltiesSalary' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD PenaltiesSalary FLOAT NULL CONSTRAINT DF_Penalties_PenaltiesSalary DEFAULT ((0)); GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Approved' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD Approved TINYINT NULL CONSTRAINT DF_Penalties_Approved DEFAULT ((0)); GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='DetailsFrom' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD DetailsFrom FLOAT NULL CONSTRAINT DF_Penalties_DetailsFrom DEFAULT ((0)); GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='DetailsTo' AND Object_ID=OBJECT_ID('Penalties')) ALTER TABLE Penalties ADD DetailsTo FLOAT NULL CONSTRAINT DF_Penalties_DetailsTo DEFAULT ((0)); GO IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'Colsed' AND Object_ID = Object_ID('dbo.Penalties')) BEGIN ALTER TABLE dbo.Penalties DROP COLUMN Colsed; END GO INSERT[dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription]) VALUES(N'HR_NumberOfMonthsForThePenalty',N'Number Of Months For The Penalty',N'عدد شهور سقوط الجزاء',N'6',15,3,2,1,Null,Null,Null,N'Number Of Months For The Penalty To Lapse') Go ----------------------------------------- End 2025-12-10 ----------------- ----------------------------------------- ’Mostafa 2025-12-14 ----------------- BEGIN TRANSACTION; SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON; SET NUMERIC_ROUNDABORT OFF; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.LeaveTransaction'); -- Drop FK inside this table (outing) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.LeaveTransaction'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.objects WHERE parent_object_id = OBJECT_ID('dbo.LeaveTransaction') AND type IN('PK','UQ','C','D'); EXEC(@s); -- Drop temp table if exists IF OBJECT_ID(N'dbo.Tmp_LeaveTransaction', 'U') IS NOT NULL DROP TABLE dbo.Tmp_LeaveTransaction; CREATE TABLE dbo.Tmp_LeaveTransaction ( LeaveTransactionCode int NOT NULL IDENTITY (1, 1), Serial float(53) NOT NULL, EmployeeCode int NOT NULL, VacationCode tinyint NULL, FromDate datetime NOT NULL, ToDate datetime NULL, FromDateHijri nvarchar(10) NULL, ToDateHijri nvarchar(10) NULL, Comment nvarchar(250) NULL, DaysCount float(53) NULL, AddBy nvarchar(200) NULL, AddDate datetime NULL, LastChangeBy nvarchar(200) NULL, ChangeDate datetime NULL, VacationRequestSerial float(53) NULL, MainType tinyint NULL, [Return] bit NULL, RequiredReturnNotification bit NULL, FCClosed bit NULL, VacationStatusCode int NULL ) ON [PRIMARY] ALTER TABLE dbo.Tmp_LeaveTransaction SET (LOCK_ESCALATION = TABLE) ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_Serial DEFAULT ((0)) FOR Serial ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_EmployeeCode DEFAULT ((0)) FOR EmployeeCode ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_VacationCode DEFAULT ((0)) FOR VacationCode ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_Return DEFAULT ((0)) FOR [Return] ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_RequiredReturnNotification DEFAULT ((0)) FOR RequiredReturnNotification ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_FCClosed DEFAULT ((0)) FOR FCClosed ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_MainType DEFAULT ((0)) FOR MainType ALTER TABLE dbo.Tmp_LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_VacationStatusCode DEFAULT ((0)) FOR VacationStatusCode -- Note: No need for IDENTITY_INSERT since we're not inserting into the new identity column -- The identity column will auto-generate new values IF EXISTS(SELECT * FROM dbo.LeaveTransaction) EXEC('INSERT INTO dbo.Tmp_LeaveTransaction (Serial, EmployeeCode, VacationCode, FromDate, ToDate, FromDateHijri, ToDateHijri, Comment, DaysCount, AddBy, AddDate, LastChangeBy, ChangeDate, VacationRequestSerial, MainType, [Return], RequiredReturnNotification, FCClosed, VacationStatusCode) SELECT Serial, EmployeeCode, VacationCode, FromDate, ToDate, FromDateHijri, ToDateHijri, Comment, DaysCount, AddBy, AddDate, LastChangeBy, ChangeDate, VacationRequestSerial, MainType, [Return], RequiredReturnNotification, FCClosed, VacationStatusCode FROM dbo.LeaveTransaction WITH (HOLDLOCK TABLOCKX)') DROP TABLE dbo.LeaveTransaction EXECUTE sp_rename N'dbo.Tmp_LeaveTransaction', N'LeaveTransaction', 'OBJECT' ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT PK_LeaveTransaction PRIMARY KEY CLUSTERED (LeaveTransactionCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] COMMIT IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ADaysCount' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD ADaysCount float(53) NULL CONSTRAINT DF_LeaveTransaction_ADaysCount DEFAULT (0); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='PenaltySerial' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD PenaltySerial float(53) NULL CONSTRAINT DF_LeaveTransaction_PenaltySerial DEFAULT (0); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_LeaveTransaction_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD CreatedBy INT NOT NULL CONSTRAINT DF_LeaveTransaction_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('LeaveTransaction')) BEGIN ALTER TABLE LeaveTransaction ADD ModificationDate DATETIME NULL; END GO UPDATE dbo.LeaveTransaction SET CreationDate = AddDate WHERE AddDate IS NOT NULL; GO UPDATE dbo.LeaveTransaction SET ModificationDate = ChangeDate WHERE ChangeDate IS NOT NULL GO IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'AddBy' AND Object_ID = Object_ID('dbo.LeaveTransaction')) BEGIN ALTER TABLE dbo.LeaveTransaction DROP COLUMN AddBy; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'AddDate' AND Object_ID = Object_ID('dbo.LeaveTransaction')) BEGIN ALTER TABLE dbo.LeaveTransaction DROP COLUMN AddDate; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'LastChangeBy' AND Object_ID = Object_ID('dbo.LeaveTransaction')) BEGIN ALTER TABLE dbo.LeaveTransaction DROP COLUMN LastChangeBy; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'ChangeDate' AND Object_ID = Object_ID('dbo.LeaveTransaction')) BEGIN ALTER TABLE dbo.LeaveTransaction DROP COLUMN ChangeDate; END IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.LeaveTransaction')) BEGIN ALTER TABLE dbo.LeaveTransaction DROP COLUMN MDT; END GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.LeaveTransaction') AND c.name = N'MainType') BEGIN ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_MainType DEFAULT (0) FOR MainType; END GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.LeaveTransaction') AND c.name = N'VacationStatusCode ') BEGIN ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT DF_LeaveTransaction_VacationStatusCode DEFAULT (0) FOR VacationStatusCode ; END go DELETE FROM LeaveTransaction WHERE NOT EXISTS ( SELECT 1 FROM Employees vt WHERE vt.EmployeeCode = LeaveTransaction.EmployeeCode); DELETE FROM LeaveTransaction WHERE NOT EXISTS ( SELECT 1 FROM VacationType vt WHERE vt.VacationCode = LeaveTransaction.VacationCode); Go BEGIN TRANSACTION; SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON; SET NUMERIC_ROUNDABORT OFF; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; ALTER TABLE dbo.VacationType SET (LOCK_ESCALATION = TABLE); ALTER TABLE dbo.Employees SET (LOCK_ESCALATION = TABLE); ALTER TABLE dbo.SecurityUser SET (LOCK_ESCALATION = TABLE); ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT FK_LeaveTransaction_Employees FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION; ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT FK_LeaveTransaction_SecurityUserCreatedBy FOREIGN KEY (CreatedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE NO ACTION ON DELETE NO ACTION; ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT FK_LeaveTransaction_SecurityUserModifiedBy FOREIGN KEY (ModifiedBy) REFERENCES dbo.SecurityUser(SecurityUserCode) ON UPDATE NO ACTION ON DELETE NO ACTION; ALTER TABLE dbo.LeaveTransaction ADD CONSTRAINT FK_LeaveTransaction_VacationType FOREIGN KEY (VacationCode) REFERENCES dbo.VacationType(VacationCode) ON UPDATE NO ACTION ON DELETE NO ACTION; ALTER TABLE dbo.LeaveTransaction SET (LOCK_ESCALATION = TABLE); COMMIT TRANSACTION; GO IF OBJECT_ID(N'dbo.VacationSpecialBalance', N'U') IS NULL BEGIN CREATE TABLE [dbo].[VacationSpecialBalance]( [Serial] [int] NOT NULL, [EmployeeCode] [int] NULL, [PYear] [int] NULL, [Period] [tinyint] NULL, [Balance] [float] NULL, [VacationCollectCode] [tinyint] NULL, [Reason] [nvarchar](200) NULL, [AddBy] [nvarchar](20) NULL, [AddDate] [datetime] NULL, [LastChangeBy] [nvarchar](20) NULL, [ChangeDate] [datetime] NULL, [VacationEffectCode] [tinyint] NULL, [TType] [int] NULL, [SpecialDate] [datetime] NULL, [SpecialDateHijri] [nvarchar](10) NULL, [MDT] [datetime] NULL, [ImportNumber] [int] NULL, [WebRequestType] [float] NULL, [WebRequestId] [float] NULL, [LinkSerial] [float] NULL, CONSTRAINT [PK_VacationSpecialBalance] PRIMARY KEY CLUSTERED ( [Serial] ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END GO IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'Periods' AND schema_id = SCHEMA_ID('dbo')) BEGIN CREATE TABLE [dbo].[Periods]( [Period] [tinyint] NOT NULL, [Name] [nvarchar](20) NULL, [Arabic_Name] [nvarchar](20) NULL, [TransactionDayes] [tinyint] NULL, [MonthDays] [int] NULL, [MDT] [datetime] NULL, [TransactionDays] [tinyint] NULL, CONSTRAINT [PK_Periods] PRIMARY KEY CLUSTERED ([Period])); END GO IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationCollectTypes' AND schema_id = SCHEMA_ID('dbo')) BEGIN CREATE TABLE [dbo].[VacationCollectTypes]( [VacationCollectCode] [tinyint] NOT NULL, [Name] [nvarchar](30) NULL, [Arabic_Name] [nvarchar](30) NULL, [MDT] [datetime] NULL, CONSTRAINT PK_VacationCollectTypes PRIMARY KEY (VacationCollectCode)); END GO IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationEffect' AND schema_id = SCHEMA_ID('dbo')) BEGIN CREATE TABLE [dbo].[VacationEffect]( [VacationEffectCode] [tinyint] NOT NULL, [Name] [nvarchar](50) NULL, [Arabic_Name] [nvarchar](50) NULL, [MDT] [datetime] NULL, CONSTRAINT PK_VacationEffect PRIMARY KEY (VacationEffectCode)); END GO /* ================================ 2) INSERT PERIODS (IF NOT EXISTS) ================================ */ INSERT INTO dbo.Periods (Period, Name, Arabic_Name, TransactionDayes, MonthDays, MDT) SELECT v.*FROM (VALUES (1 ,N'January' ,N'يناير' ,31,31,'2021-03-25'), (2 ,N'February' ,N'فبراير' ,31,28,'2021-03-25'), (3 ,N'March' ,N'مارس' ,28,31,'2021-03-25'), (4 ,N'April' ,N'أبريل' ,31,30,'2021-03-25'), (5 ,N'May' ,N'مايو' ,30,31,'2021-03-25'), (6 ,N'June' ,N'يونيو' ,31,30,'2021-03-25'), (7 ,N'July' ,N'يوليو' ,30,31,'2021-03-25'), (8 ,N'August' ,N'أغسطس' ,31,31,'2021-03-25'), (9 ,N'September',N'سبتمبر' ,31,30,'2021-03-25'), (10,N'October' ,N'أكتوبر' ,30,31,'2021-03-25'), (11,N'November' ,N'نوفمبر' ,31,30,'2021-03-25'), (12,N'December' ,N'ديسمبر' ,30,31,'2021-03-25') ) v(Period, Name, Arabic_Name, TransactionDayes, MonthDays, MDT) WHERE NOT EXISTS (SELECT 1 FROM dbo.Periods p WHERE p.Period = v.Period); GO /* ================================ 4) INSERT VACATION EFFECT (SAFE) ================================ */ INSERT INTO dbo.VacationEffect (VacationEffectCode, Name, Arabic_Name, MDT) SELECT v.* FROM (VALUES (1,N'Discount',N'الخصم','2019-11-10'), (2,N'Due',N'المستحق','2019-11-10'), (3,N'Consumption',N'المستهلك','2019-11-10') ) v(VacationEffectCode, Name, Arabic_Name, MDT)WHERE NOT EXISTS (SELECT 1 FROM dbo.VacationEffect e WHERE e.VacationEffectCode = v.VacationEffectCode); GO IF NOT EXISTS (SELECT 1 FROM sys.tables WHERE name = 'VacationSpecialBalance' AND schema_id = SCHEMA_ID('dbo')) BEGIN CREATE TABLE [dbo].[VacationSpecialBalance]( [Serial] [int] NULL, [EmployeeCode] [int] NULL, [PYear] [int] NULL, [Period] [tinyint] NULL, [Balance] [float] NULL, [VacationCollectCode] [tinyint] NULL, [Reason] [nvarchar](200) NULL, [AddBy] [nvarchar](20) NULL, [AddDate] [datetime] NULL, [LastChangeBy] [nvarchar](20) NULL, [ChangeDate] [datetime] NULL, [VacationEffectCode] [tinyint] NULL, [TType] [int] NULL, [SpecialDate] [datetime] NULL, [SpecialDateHijri] [nvarchar](10) NULL, [MDT] [datetime] NULL, [ImportNumber] [int] NULL, [WebRequestType] [float] NULL, [WebRequestId] [float] NULL, [LinkSerial] [float] NULL ) ON [PRIMARY] END GO IF COL_LENGTH('VacationType', 'WF_IsMultiSelect') IS NOT NULL UPDATE VacationType SET WF_IsMultiSelect = ISNULL(WF_IsMultiSelect, 0); GO IF COL_LENGTH('VacationType', 'InsideVacationPlan') IS NOT NULL UPDATE VacationType SET InsideVacationPlan = ISNULL(InsideVacationPlan, 0); GO IF COL_LENGTH('VacationType', 'HaveDifferentTrack') IS NOT NULL UPDATE VacationType SET HaveDifferentTrack = ISNULL(HaveDifferentTrack, 0); GO IF COL_LENGTH('VacationType', 'EffectInAppraisal') IS NOT NULL UPDATE VacationType SET EffectInAppraisal = ISNULL(EffectInAppraisal, 0); GO IF COL_LENGTH('VacationType', 'ShowVacationTime') IS NOT NULL UPDATE VacationType SET ShowVacationTime = ISNULL(ShowVacationTime, 0); GO IF COL_LENGTH('VacationType', 'ShowSalaryAdvanceRequired') IS NOT NULL UPDATE VacationType SET ShowSalaryAdvanceRequired = ISNULL(ShowSalaryAdvanceRequired, 0); GO IF COL_LENGTH('VacationType', 'ShowAirTicketRequired') IS NOT NULL UPDATE VacationType SET ShowAirTicketRequired = ISNULL(ShowAirTicketRequired, 0); GO IF COL_LENGTH('VacationType', 'CanSplitVacation') IS NOT NULL UPDATE VacationType SET CanSplitVacation = ISNULL(CanSplitVacation, 0); GO IF COL_LENGTH('VacationType', 'IncludeWorkingHour') IS NOT NULL UPDATE VacationType SET IncludeWorkingHour = ISNULL(IncludeWorkingHour, 0); GO IF COL_LENGTH('VacationType', 'ReplacementEmployeeRequired') IS NOT NULL UPDATE VacationType SET ReplacementEmployeeRequired = ISNULL(ReplacementEmployeeRequired, 0); GO IF COL_LENGTH('VacationType', 'VacationsPlan') IS NOT NULL UPDATE VacationType SET VacationsPlan = ISNULL(VacationsPlan, 0); GO IF COL_LENGTH('VacationType', 'DeductThisDaysInAirTicket') IS NOT NULL UPDATE VacationType SET DeductThisDaysInAirTicket = ISNULL(DeductThisDaysInAirTicket, 0); GO IF COL_LENGTH('VacationType', 'AbsentPenalty') IS NOT NULL UPDATE VacationType SET AbsentPenalty = ISNULL(AbsentPenalty, 0); GO IF COL_LENGTH('VacationType', 'AffectSCBonus') IS NOT NULL UPDATE VacationType SET AffectSCBonus = ISNULL(AffectSCBonus, 0); GO IF COL_LENGTH('VacationType', 'UseProfileSpecialRate') IS NOT NULL UPDATE VacationType SET UseProfileSpecialRate = ISNULL(UseProfileSpecialRate, 0); GO IF COL_LENGTH('VacationType', 'UseTimePenaltiesActions') IS NOT NULL UPDATE VacationType SET UseTimePenaltiesActions = ISNULL(UseTimePenaltiesActions, 0); GO IF COL_LENGTH('VacationType', 'DuplicateCalculate') IS NOT NULL UPDATE VacationType SET DuplicateCalculate = ISNULL(DuplicateCalculate, 0); GO IF COL_LENGTH('VacationType', 'ConvertCountOfDaysTo30') IS NOT NULL UPDATE VacationType SET ConvertCountOfDaysTo30 = ISNULL(ConvertCountOfDaysTo30, 0); GO IF COL_LENGTH('VacationType', 'HideInLeavesAndVacationsScreen') IS NOT NULL UPDATE VacationType SET HideInLeavesAndVacationsScreen = ISNULL(HideInLeavesAndVacationsScreen, 0); GO IF COL_LENGTH('VacationType', 'CantAddRequestForEmpHaveFingerPrint') IS NOT NULL UPDATE VacationType SET CantAddRequestForEmpHaveFingerPrint = ISNULL(CantAddRequestForEmpHaveFingerPrint, 0); GO IF COL_LENGTH('VacationType', 'DisplayInMovementVacations') IS NOT NULL UPDATE VacationType SET DisplayInMovementVacations = ISNULL(DisplayInMovementVacations, 0); GO IF COL_LENGTH('VacationType', 'IsMissionPlace') IS NOT NULL UPDATE VacationType SET IsMissionPlace = ISNULL(IsMissionPlace, 0); GO IF COL_LENGTH('VacationType', 'ShowVisaRequired') IS NOT NULL UPDATE VacationType SET ShowVisaRequired = ISNULL(ShowVisaRequired, 0); GO -- Sequence IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE name = N'Sequence' AND object_id = OBJECT_ID(N'dbo.VacationType') ) BEGIN ALTER TABLE dbo.VacationType ADD Sequence INT NOT NULL CONSTRAINT DF_VacationType_Sequence DEFAULT (0); END GO -- MaxDaysPerWeek IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE name = N'MaxDaysPerWeek' AND object_id = OBJECT_ID(N'dbo.VacationType') ) BEGIN ALTER TABLE dbo.VacationType ADD MaxDaysPerWeek INT NOT NULL CONSTRAINT DF_VacationType_MaxDaysPerWeek DEFAULT (0); END GO -- DayStartOfWeekCode IF NOT EXISTS ( SELECT 1 FROM sys.columns WHERE name = N'DayStartOfWeekCode' AND object_id = OBJECT_ID(N'dbo.VacationType') ) BEGIN ALTER TABLE dbo.VacationType ADD DayStartOfWeekCode INT NOT NULL CONSTRAINT DF_VacationType_DayStartOfWeekCode DEFAULT (1); END GO IF COL_LENGTH('VacationType', 'ReferenceToCalculateForTerminatedUntilEndOfMonth') IS NOT NULL UPDATE VacationType SET ReferenceToCalculateForTerminatedUntilEndOfMonth = ISNULL(ReferenceToCalculateForTerminatedUntilEndOfMonth, 0); GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType') AND c.name = N'ReferenceToCalculateForTerminatedUntilEndOfMonth') BEGIN ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_ReferenceToCalculateForTerminatedUntilEndOfMonth DEFAULT (0) FOR ReferenceToCalculateForTerminatedUntilEndOfMonth; END GO IF COL_LENGTH('VacationType', 'DiffrentEffectAccrodingToPayCode') IS NOT NULL UPDATE VacationType SET DiffrentEffectAccrodingToPayCode = ISNULL(DiffrentEffectAccrodingToPayCode, 0); GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType') AND c.name = N'DiffrentEffectAccrodingToPayCode') BEGIN ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_DiffrentEffectAccrodingToPayCode DEFAULT (0) FOR DiffrentEffectAccrodingToPayCode; END GO IF COL_LENGTH('VacationType', 'DropDayoffBalance') IS NOT NULL UPDATE VacationType SET DropDayoffBalance = ISNULL(DropDayoffBalance, 0); GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType') AND c.name = N'DropDayoffBalance') BEGIN ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_DropDayoffBalance DEFAULT (0) FOR DropDayoffBalance; END GO IF COL_LENGTH('VacationType', 'ForReadOnlyInAttendanceSystem') IS NOT NULL UPDATE VacationType SET ForReadOnlyInAttendanceSystem = ISNULL(ForReadOnlyInAttendanceSystem, 0); GO IF NOT EXISTS (SELECT 1 FROM sys.default_constraints dc INNER JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id WHERE dc.parent_object_id = OBJECT_ID(N'dbo.VacationType') AND c.name = N'ForReadOnlyInAttendanceSystem') BEGIN ALTER TABLE dbo.VacationType ADD CONSTRAINT DF_VacationType_ForReadOnlyInAttendanceSystem DEFAULT (0) FOR ForReadOnlyInAttendanceSystem; END GO ----------------------------------------- End 2025-12-14 ----------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1217,N'Funcations',N'المهام',27,NULL,NULL,N'fa-solid fa-list-check',NULL,2,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1217,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4312,N'المهام',N'Funcations',1217,1) Go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1218,N'Human Resource',N'شئون العاملين',1217,NULL,NULL,N'fa-solid fa-users-gear',NULL,1,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1218,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4313,N'شئون العاملين',N'Human Resource',1218,1) Go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1221,N'Penalties',N'الجزاءات',1218,N'/Personal/Penalties',N'Index',N'fa-solid fa-users-slash',N'get',1,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1221,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4315,N'الجزاءات',N'Penalties',1221,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4316,N'عرض الجزاءات',N'View Penalties',1221,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4317,N'إضافة الجزاءات',N'Add Penalties',1221,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4318,N'تعديل الجزاءات',N'Edit Penalties',1221,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4319,N'حذف الجزاءات',N'Delete Penalties',1221,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4320,N'حفظ الجزاءات',N'Save Penalties',1221,2) Go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1219,N'Leaves And Vacations',N'العطلات والاجازات',1217,NULL,NULL,N'fa-solid fa-plane-departure',NULL,2,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1219,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4314,N'العطلات والاجازات',N'Leaves And Vacations',1219,1) Go INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1222,N'Leaves And Vacations',N'الغياب والاجازات',1219,N'/Personal/LeaveTransaction',N'Index',N'fa-solid fa-umbrella-beach',N'get',1,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1222,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4321,N'الغياب والاجازات',N'Leaves And Vacations',1222,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4322,N'عرض الغياب والاجازات',N'View Leaves And Vacations',1222,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4323,N'إضافة الغياب والاجازات',N'Add Leaves And Vacations',1222,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4324,N'تعديل الغياب والاجازات',N'Edit Leaves And Vacations',1222,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4325,N'حذف الغياب والاجازات',N'Delete Leaves And Vacations',1222,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4326,N'حفظ الغياب والاجازات',N'Save Leaves And Vacations',1222,2) GO ----------------------------------------- Ali 2025-12-21 ----------------- INSERT[dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription]) VALUES(N'SyncDataFromERPToPayroll',N'Sync Data From ERP To Payroll',N'مزامنة البيانات من ERP إلى Payroll',N'false',1,68,1,1,Null,Null,Null,N'مزامنة البيانات من ERP إلى Payroll') Go ----------------------------------------- End 2025-12-21 ----------------- ----------------------------------------- Mostafa 2025-12-23 ----------------- BEGIN TRANSACTION; SET QUOTED_IDENTIFIER ON; SET ARITHABORT ON; SET NUMERIC_ROUNDABORT OFF; SET CONCAT_NULL_YIELDS_NULL ON; SET ANSI_NULLS ON; SET ANSI_PADDING ON; SET ANSI_WARNINGS ON; DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id) + '].[' + OBJECT_NAME(fk.parent_object_id) + '] DROP CONSTRAINT [' + fk.name + ']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.ResignationReasons'); -- Drop FK inside this table (outing) SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.ResignationReasons'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s += 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] DROP CONSTRAINT [' + name + ']; ' FROM sys.objects WHERE parent_object_id = OBJECT_ID('dbo.ResignationReasons') AND type IN('PK','UQ','C','D'); EXEC(@s); -- Drop temp table if exists IF OBJECT_ID(N'dbo.Tmp_ResignationReasons', 'U') IS NOT NULL DROP TABLE dbo.Tmp_ResignationReasons; CREATE TABLE dbo.Tmp_ResignationReasons ( ResignationCode tinyint NOT NULL IDENTITY (0, 1), Name nvarchar(150) NULL, Arabic_Name nvarchar(150) NULL, GeneralTerminatedCode int NULL, NotCalculatedWithTurnover bit NULL, HaveDifferentTrack bit NOT NULL, HideFromRequest bit NOT NULL) ON [PRIMARY] ALTER TABLE dbo.Tmp_ResignationReasons SET (LOCK_ESCALATION = TABLE) ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT DF_ResignationReasons_NotCalculatedWithTurnover DEFAULT ((0)) FOR NotCalculatedWithTurnover ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT DF_ResignationReasons_HaveDifferentTrack DEFAULT (CONVERT([bit],(0))) FOR HaveDifferentTrack ALTER TABLE dbo.Tmp_ResignationReasons ADD CONSTRAINT DF_ResignationReasons_HideFromRequest DEFAULT (CONVERT([bit],(0))) FOR HideFromRequest SET IDENTITY_INSERT dbo.Tmp_ResignationReasons ON IF EXISTS(SELECT * FROM dbo.ResignationReasons) EXEC('INSERT INTO dbo.Tmp_ResignationReasons (ResignationCode, Name, Arabic_Name, GeneralTerminatedCode, NotCalculatedWithTurnover, HaveDifferentTrack, HideFromRequest) SELECT ResignationCode, Name, Arabic_Name, GeneralTerminatedCode, NotCalculatedWithTurnover, HaveDifferentTrack, HideFromRequest FROM dbo.ResignationReasons WITH (HOLDLOCK TABLOCKX)') SET IDENTITY_INSERT dbo.Tmp_ResignationReasons OFF IF OBJECT_ID(N'dbo.WF_ResignationRequest', 'U') IS NOT NULL AND EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_WF_ResignationRequest_ResignationReasons' AND parent_object_id = OBJECT_ID(N'dbo.WF_ResignationRequest')) BEGIN ALTER TABLE dbo.WF_ResignationRequest DROP CONSTRAINT FK_WF_ResignationRequest_ResignationReasons; END DROP TABLE dbo.ResignationReasons EXECUTE sp_rename N'dbo.Tmp_ResignationReasons', N'ResignationReasons', 'OBJECT' ALTER TABLE dbo.ResignationReasons ADD CONSTRAINT PK_ResignationReasons PRIMARY KEY CLUSTERED (ResignationCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] COMMIT BEGIN TRANSACTION; IF OBJECT_ID(N'dbo.WF_ResignationRequest', 'U') IS NOT NULL AND OBJECT_ID(N'dbo.ResignationReasons', 'U') IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM sys.foreign_keys WHERE name = N'FK_WF_ResignationRequest_ResignationReasons') BEGIN ALTER TABLE dbo.WF_ResignationRequest ADD CONSTRAINT FK_WF_ResignationRequest_ResignationReasons FOREIGN KEY (ResignationCode) REFERENCES dbo.ResignationReasons (ResignationCode) ON UPDATE NO ACTION ON DELETE NO ACTION; END ALTER TABLE dbo.WF_ResignationRequest SET (LOCK_ESCALATION = TABLE); COMMIT GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CentralCode' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD CentralCode INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Serial' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD Serial NVARCHAR(250) NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD Closed BIT NULL CONSTRAINT DF_ResignationReasons_Closed DEFAULT (0); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_ResignationReasons_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD CreatedBy INT NOT NULL CONSTRAINT DF_ResignationReasons_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('ResignationReasons')) BEGIN ALTER TABLE ResignationReasons ADD ModificationDate DATETIME NULL; END GO IF EXISTS(SELECT 1 FROM ResignationReasons WHERE Serial IS NULL) BEGIN UPDATE ResignationReasons SET Serial = ResignationCode WHERE Serial IS NULL; END GO UPDATE ResignationReasons SET Closed = 0WHERE Closed IS NULL; GO ----------------------------------------- End 2025-12-23 ----------------- ----------------------------------------- Mostafa 2025-12-24 ----------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1226,N'Resignation',N'الاستقالة',1218,N'/Personal/EmployeeResignation',N'Index','fa-solid fa-user-large-slash',N'get',2,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1226,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4327,N'الاستقالة',N'Resignation',1226,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4328,N'عرض الاستقالة',N'View Resignation',1226,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4329,N'إضافة الاستقالة',N'Add Resignation',1226,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4330,N'تعديل الاستقالة',N'Edit Resignation',1226,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4332,N'حذف الاستقالة',N'Delete Resignation',1226,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4331,N'حفظ الاستقالة',N'Save Resignation',1226,2) GO ----------------------------------------- End 2025-12-24 ----------------- ----------------------------------------- Mostafa 2025-12-29 ----------------- INSERT[dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription]) VALUES(N'HR_ResignedWithinTheMonth',N'Resigned Within The Month (Employee Resignation)',N'استقالت خلال الشهر (استقالة موظف)',N'False',15,4,1,1,Null,Null,Null,N'الموظفين الذين قاموا بتقديم استقالتهم أو تم قبول استقالتهم خلال الشهر الحالي') Go ----------------------------------------- End 2025-12-29 ----------------- ----------------------------------------- AK 2025-12-30 -------------------- INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1227,N'Salary Increase and Change Status',N'زيادة الراتب وتغيير الحالة',1218,N'/Personal/Promotion',N'Index',N'fa fa-thumb-tack',N'get',3,1,1) GO INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1227,22) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4333,N'زيادة الراتب وتغيير الحالة',N'Salary Increase and Change Status',1227,1) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4334,N'عرض زيادة الراتب وتغيير الحالة',N'View Salary Increase and Change Status',1227,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4335,N'إضافة زيادة الراتب وتغيير الحالة',N'Add Salary Increase and Change Status',1227,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4336,N'تعديل زيادة الراتب وتغيير الحالة',N'Edit Salary Increase and Change Status',1227,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4337,N'حذف زيادة الراتب وتغيير الحالة',N'Delete Salary Increase and Change Status',1227,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4338,N'حفظ زيادة الراتب وتغيير الحالة',N'Save Salary Increase and Change Status',1227,2) GO ----------------------------------------- AK 2025-12-30 --------------------- ----------------------------------------- Mostafa 2025-12-30 --------------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1229,N'Keeping',N'العهد',1218,N'/Personal/Keeping',N'Index',N'fa-solid fa-box-open',N'get',4,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1229,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4345,N'العهد',N'Keeping',1229,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4346,N'عرض العهد',N'View Keeping',1229,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4347,N'إضافة العهد',N'Add Keeping',1229,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4348,N'تعديل العهد',N'Edit Keeping',1229,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4349,N'حذف العهد',N'Delete Keeping',1229,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4350,N'حفظ العهد',N'Save Keeping',1229,2) GO INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1228,N'Keeping Types',N'انواع العهد',1062,N'/Personal/KeepingType',N'Index',N'fa-solid fa-list-check',N'get',16,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1228,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4339,N'انواع العهد',N'Keeping Types',1228,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4340,N'عرض انواع العهد',N'View Keeping Types',1228,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4341,N'إضافة انواع العهد',N'Add Keeping Types',1228,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4342,N'تعديل انواع العهد',N'Edit Keeping Types',1228,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4343,N'حذف انواع العهد',N'Delete Keeping Types',1228,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4344,N'حفظ انواع العهد',N'Save Keeping Types',1228,2) GO INSERT[dbo].[ConfigParameter] ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId],[ConfigDescription]) VALUES(N'HR_ResignationAllowedWithKeeping',N'Can A Resignation Be Submitted If There Is A Keeping?',N'يمكن تقديم الاستقالة في حالة وجود عهد ؟',N'False',15,5,1,1,Null,Null,Null,N'يمكن تقديم الاستقالة في حالة وجود عهد ؟') Go ----------------------------------------- End 2025-12-30 --------------------- ----------------------------------------- Mostafa 2026-01-01 --------------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1230,N'Resignation Reasons',N'اسباب الاستقالة',1062,N'/Personal/ResignationReason',N'Index',N'fa-solid fa-triangle-exclamation',N'get',17,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1230,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4351,N'اسباب الاستقالة',N'Resignation Reasonss',1230,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4352,N'عرض اسباب الاستقالة',N'View Resignation Reasons',1230,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4353,N'إضافة اسباب الاستقالة',N'Add Resignation Reasons',1230,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4354,N'تعديل اسباب الاستقالة',N'Edit Resignation Reasons',1230,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4355,N'حذف اسباب الاستقالة',N'Delete Resignation Reasons',1230,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4356,N'حفظ اسباب الاستقالة',N'Save Resignation Reasons',1230,2) GO ----------------------------------------- End 2026-01-01--------------------- ----------------------------------------- Mostafa 2026-01-04--------------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1231,N'Health Card',N'الشهادة الصحية',1218,N'/Personal/HealthCard',N'Index',N'fa-solid fa-hospital-user',N'get',5,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1231,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4357,N'الشهادة الصحية',N'Health Card',1231,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4358,N'عرض الشهادة الصحية',N'View Health Card',1231,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4359,N'إضافة الشهادة الصحية',N'Add Health Card',1231,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4360,N'تعديل الشهادة الصحية',N'Edit Health Card',1231,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4361,N'حذف الشهادة الصحية',N'Delete Health Card',1231,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4362,N'حفظ الشهادة الصحية',N'Save Health Card',1231,2) GO IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.HealthCard')) BEGIN ALTER TABLE dbo.HealthCard DROP COLUMN MDT; END GO DELETE hc FROM dbo.HealthCard hc WHERE hc.EmployeeCode IS NULL OR NOT EXISTS ( SELECT 1 FROM dbo.Employees e WHERE e.EmployeeCode = hc.EmployeeCode); GO BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.HealthCard'); -- Drop FK inside this table (outgoing) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.HealthCard'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.HealthCard') AND type IN('PK','UQ','C','D'); EXEC(@s); IF OBJECT_ID(N'dbo.Tmp_HealthCard', 'U') IS NOT NULL DROP TABLE dbo.Tmp_HealthCard; CREATE TABLE dbo.Tmp_HealthCard( HealthCardCode int NOT NULL IDENTITY (1, 1), EmployeeCode int NOT NULL, CardNumber nvarchar(50) NULL, IssueDate datetime NULL, ExpireDate datetime NULL, IssuePlace nvarchar(150) NULL, IssueDateHijri nvarchar(10) NULL, ExpireDateHijri nvarchar(10) NULL, EndTrainingDate datetime NULL, EndTrainingDateHijri nvarchar(10) NULL, InvoiceNumber nvarchar(100) NULL, InvoiceDate datetime NULL, InvoiceDateHijri nvarchar(10) NULL ) ON [PRIMARY] ALTER TABLE dbo.Tmp_HealthCard SET (LOCK_ESCALATION = TABLE) SET IDENTITY_INSERT dbo.Tmp_HealthCard OFF IF EXISTS(SELECT * FROM dbo.HealthCard) EXEC('INSERT INTO dbo.Tmp_HealthCard (EmployeeCode, CardNumber, IssueDate, ExpireDate, IssuePlace, IssueDateHijri, ExpireDateHijri, EndTrainingDate, EndTrainingDateHijri, InvoiceNumber, InvoiceDate, InvoiceDateHijri) SELECT EmployeeCode, CardNumber, IssueDate, ExpireDate, IssuePlace, IssueDateHijri, ExpireDateHijri, EndTrainingDate, EndTrainingDateHijri, InvoiceNumber, InvoiceDate, InvoiceDateHijri FROM dbo.HealthCard WITH (HOLDLOCK TABLOCKX)') DROP TABLE dbo.HealthCard EXECUTE sp_rename N'dbo.Tmp_HealthCard', N'HealthCard', 'OBJECT' ALTER TABLE dbo.HealthCard ADD CONSTRAINT PK_HealthCard PRIMARY KEY CLUSTERED (HealthCardCode) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] COMMIT GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('HealthCard')) BEGIN ALTER TABLE HealthCard ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_HealthCard_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('HealthCard')) BEGIN ALTER TABLE HealthCard ADD CreatedBy INT NOT NULL CONSTRAINT DF_HealthCard_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('HealthCard')) BEGIN ALTER TABLE HealthCard ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='Closed' AND Object_ID=Object_ID('HealthCard')) BEGIN ALTER TABLE HealthCard ADD Closed BIT NULL CONSTRAINT DF_HealthCard_Closed DEFAULT (0); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('HealthCard')) BEGIN ALTER TABLE HealthCard ADD ModificationDate DATETIME NULL; END GO ALTER TABLE dbo.HealthCard ADD CONSTRAINT FK_HealthCard_EmployeeCode FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION; go ----------------------------------------- End 2026-01-04--------------------- ----------------------------------------- AK 2026-01-05 -------------------- INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1233,N'Change department status',N'تغيير الحالة الإدارية',1218,N'/Personal/ChangeDepartmentStatus',N'Index',N'fa fa-thumb-tack',N'get',1,1,1) GO INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1233,22) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4369,N'تغيير الحالة الإدارية',N'Change department status',1233,1) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4370,N'عرض تغيير الحالة الإدارية',N'View Change department status',1233,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4371,N'إضافة تغيير الحالة الإدارية',N'Add Change department status',1233,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4372,N'تعديل تغيير الحالة الإدارية',N'Edit Change department status',1233,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4373,N'حذف تغيير الحالة الإدارية',N'Delete Change department status',1233,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4374,N'حفظ تغيير الحالة الإدارية',N'Save Change department status',1233,2) GO ----------------------------------------- AK 2026-01-05 --------------------- ----------------------------------------- Mostafa 2026-01-05 --------------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1234,N'Contracts Renewal',N'تجديد العقد',1218,N'/Personal/ContractsRenewal',N'Index',N'fa-solid fa-rotate',N'get',6,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1234,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4375,N'تجديد العقد',N'Contracts Renewal',1234,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4376,N'عرض تجديد العقد',N'View Contracts Renewal',1234,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4377,N'إضافة تجديد العقد',N'Add Contracts Renewal',1234,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4378,N'تعديل تجديد العقد',N'Edit Contracts Renewal',1234,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4379,N'حذف تجديد العقد',N'Delete Contracts Renewal',1234,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4380,N'حفظ تجديد العقد',N'Save Contracts Renewal',1234,2) GO ----------------------------------------- End 2026-01-05 --------------------- ----------------------------------------- Shehab 2026-01-06 --------------------- GO INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1232, N'Marital Status', N'الحالة الاجتماعية', 1218, N'/Personal/ChangeMaritalStatus', N'Index', N'fas fa-wallet', N'get', 19, 1, 1) Go INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1232, 22) GO Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4363,N'تغيير الحالة الاجتماعية',N'Change Marital Status',1232,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4364,N'عرض تعيير الحالة الاجتماعية',N'View Change Marital Status',1232,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4365,N'إضافة تغيير الحالة الاجتماعية',N'Add Change Marital Status',1232,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4366,N'تعديل تغيير الحالة الاجتماعية',N'Edit Change Marital Status',1232,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4367,N' حذف تغيير الحالة الاجتماعية ',N'Delete Change Marital Status',1232,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4368,N'حفظ تغيير الحالة الاجتماعية',N'Save Change Marital Status',1232,2) GO ----------------------------------------- End 2026-01-06 --------------------- ----------------------------------------- Mostafa 2026-01-11 --------------------- INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4393,N'اختر جميع توثيق العقد',N'Select All Contract Documentation',1234,2) GO INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4394,N'حفظ توثيق العقد',N'Save Contract Documentation',1234,2) ----------------------------------------- End 2026-01-11 --------------------- ----------------------------------------- AK 2026-01-11 -------------------- INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1236,N'Health ID Card',N'البطاقة الصحية',1218,N'/Personal/EmployeeHealthIDCard',N'Index',N'fa fa-thumb-tack',N'get',1,1,1) GO INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1236,22) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4387,N'البطاقة الصحية',N'Health ID Card',1236,1) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4388,N'عرض البطاقة الصحية',N'View Health ID Card',1236,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4389,N'إضافة البطاقة الصحية',N'Add Health ID Card',1236,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4390,N'تعديل البطاقة الصحية',N'Edit Health ID Card',1236,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4391,N'حذف البطاقة الصحية',N'Delete Health ID Card',1236,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4392,N'حفظ البطاقة الصحية',N'Save Health ID Card',1236,2) GO ----------------------------------------- AK 2026-01-11 --------------------- ----------------------------------------- Shehab 2026-01-11 -------------------- GO INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1235, N'Lapor Office Receipt', N'بيانات كعب العمل', 1218, N'/Personal/LaporOfficeReceipt', N'Index', N'fas fa-receipt', N'get', 20, 1, 1) Go INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1235, 22) GO Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4381,N'بيانات كعب العمل',N'Lapor Office Receipt',1235,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4382,N'عرض بيانات كعب العمل',N'View Lapor Office Receipt',1235,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4383,N'إضافة بيانات كعب العمل',N'Add Lapor Office Receipt',1235,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4384,N'تعديل بيانات كعب العمل',N'Edit Lapor Office Receipt',1235,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4385,N' حذف بيانات كعب العمل ',N'Delete Lapor Office Receipt',1235,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4386,N'حفظ بيانات كعب العمل',N'Save Lapor Office Receipt',1235,2) GO delete from [WorkCube] where [EmployeeCode] not in(select [EmployeeCode] from Employees) Go BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION DECLARE @s NVARCHAR(MAX)=''; -- Drop FK coming from other tables (incoming) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(fk.parent_object_id)+'].['+OBJECT_NAME(fk.parent_object_id)+'] DROP CONSTRAINT ['+fk.name+']; ' FROM sys.foreign_keys fk WHERE fk.referenced_object_id = OBJECT_ID('dbo.WorkCube'); -- Drop FK inside this table (outgoing) SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID('dbo.WorkCube'); -- Drop PK, UQ, CHECK, DEFAULT SELECT @s+= 'ALTER TABLE ['+OBJECT_SCHEMA_NAME(parent_object_id)+'].['+OBJECT_NAME(parent_object_id)+'] DROP CONSTRAINT ['+name+']; ' FROM sys.objects WHERE parent_object_id=OBJECT_ID('dbo.WorkCube') AND type IN('PK','UQ','C','D'); EXEC(@s); IF OBJECT_ID(N'dbo.Tmp_WorkCube', 'U') IS NOT NULL DROP TABLE dbo.Tmp_WorkCube; CREATE TABLE dbo.Tmp_WorkCube ( WorkCubeCode int NOT NULL IDENTITY (1, 1), EmployeeCode int NOT NULL, CubeIssueDate datetime NULL, CubeIssuePlace nvarchar(150) NULL, CubeSavedDate datetime NULL, CubeIssueNumber nvarchar(100) NULL, CubeIssueDateHijri nvarchar(10) NULL, CubeSavedDateHijri nvarchar(10) NULL, MDT datetime NULL ) ON [PRIMARY] ALTER TABLE dbo.Tmp_WorkCube SET (LOCK_ESCALATION = TABLE) SET IDENTITY_INSERT dbo.Tmp_WorkCube OFF IF EXISTS(SELECT * FROM dbo.WorkCube) EXEC('INSERT INTO dbo.Tmp_WorkCube (EmployeeCode, CubeIssueDate, CubeIssuePlace, CubeSavedDate, CubeIssueNumber, CubeIssueDateHijri, CubeSavedDateHijri, MDT) SELECT EmployeeCode, CubeIssueDate, CubeIssuePlace, CubeSavedDate, CubeIssueNumber, CubeIssueDateHijri, CubeSavedDateHijri, MDT FROM dbo.WorkCube WITH (HOLDLOCK TABLOCKX)') DROP TABLE dbo.WorkCube EXECUTE sp_rename N'dbo.Tmp_WorkCube', N'WorkCube', 'OBJECT' ALTER TABLE dbo.WorkCube ADD CONSTRAINT PK_WorkCube PRIMARY KEY CLUSTERED ( WorkCubeCode ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO COMMIT IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreationDate' AND Object_ID=Object_ID('WorkCube')) BEGIN ALTER TABLE WorkCube ADD CreationDate DATETIME NOT NULL CONSTRAINT DF_WorkCube_CreationDate DEFAULT (GETDATE()); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='CreatedBy' AND Object_ID=Object_ID('WorkCube')) BEGIN ALTER TABLE WorkCube ADD CreatedBy INT NOT NULL CONSTRAINT DF_WorkCube_CreatedBy DEFAULT (1); END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModifiedBy' AND Object_ID=Object_ID('WorkCube')) BEGIN ALTER TABLE WorkCube ADD ModifiedBy INT NULL; END GO IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE Name='ModificationDate' AND Object_ID=Object_ID('WorkCube')) BEGIN ALTER TABLE WorkCube ADD ModificationDate DATETIME NULL; END go update WorkCube set CreationDate = MDT WHERE MDT IS NOT NULL; Go IF EXISTS (SELECT 1 FROM sys.columns WHERE Name = 'MDT' AND Object_ID = Object_ID('dbo.WorkCube')) BEGIN ALTER TABLE dbo.WorkCube DROP COLUMN MDT; END GO ALTER TABLE dbo.WorkCube ADD CONSTRAINT FK_WorkCube_EmployeeCode FOREIGN KEY(EmployeeCode) REFERENCES dbo.Employees(EmployeeCode) ON UPDATE NO ACTION ON DELETE NO ACTION; go ----------------------------------------- End 2026-01-11 --------------------- ----------------------------------------- Shehab 2026-01-12 -------------------- GO INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (1237, N'Military Data', N'بيانات التجنيد', 1218, N'/Personal/MilitaryData', N'Index', N'fas fa-user-shield', N'get', 21, 1, 1) Go INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (1237, 22) GO Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4395,N'بيانات التجنيد',N'Military Data',1237,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4396,N'عرض بيانات التجنيد',N'View Military Data',1237,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4397,N'إضافة بيانات التجنيد',N'Add Military Data',1237,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4398,N'تعديل بيانات التجنيد',N'Edit Military Data',1237,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4399,N' حذف بيانات التجنيد ',N'Delete Military Data',1237,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4400,N'حفظ بيانات التجنيد',N'Save Military Data',1237,2) GO ----------------------------------------- End 2026-01-12 --------------------- ----------------------------------------- AK 2026-01-13 -------------------- INSERT INTO [SystemMenu](SystemMenuCode,Name,Arabic_Name,ParentID,ControllerName,ActionName,ImageURL,HTTPMethod,Sequence,IsVisible,ShowInPrivilegePage) VALUES (1238,N'Criminal Investigation Card',N'كرنية البحث الجنائي',1218,N'/Personal/EmployeeCriminalInvestigation',N'Index',N'fa fa-thumb-tack',N'get',1,1,1) GO INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1238,22) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4401,N'كرنية البحث الجنائي',N'Criminal Investigation Card',1238,1) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4402,N'عرض كرنية البحث الجنائي',N'View Criminal Investigation Card',1238,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4403,N'إضافة كرنية البحث الجنائي',N'Add Criminal Investigation Card',1238,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4404,N'تعديل كرنية البحث الجنائي',N'Edit Criminal Investigation Card',1238,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4405,N'حذف كرنية البحث الجنائي',N'Delete Criminal Investigation Card',1238,2) GO INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4406,N'حفظ كرنية البحث الجنائي',N'Save Criminal Investigation Card',1238,2) GO ----------------------------------------- AK 2026-01-13 --------------------- ----------------------------------------- End 2026-01-11 --------------------- ----------------------------------------- Ali 2026-01-13 --------------------- ALTER TABLE dbo.VacationType DROP CONSTRAINT DF_VacationType_DayRateNumber GO UPDATE VacationType SET DayRateNumber = NULL WHERE DayRateNumber = 0; Go ----------------------------------------- End 2026-01-13 --------------------- ----------------------------------------- Mostafa 2026-01-14 --------------------- INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES(1239,N'Payroll Basis',N'أسس الاحتساب',1215,N'/Payroll/Basis',N'Index','fa-solid fa-calculator',N'get',2,1,1) Go INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1239,22) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4407,N'أسس الاحتساب',N'Payroll Basis',1239,1) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4408,N'عرض أسس الاحتساب',N'View Payroll Basis',1239,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4409,N'إضافة أسس الاحتساب',N'Add Payroll Basis',1239,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4410,N'تعديل أسس الاحتساب',N'Edit Payroll Basis',1239,2) Go INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4411,N'حذف أسس الاحتساب',N'Delete Payroll Basis',1239,2) Go INSERT INTO [SecurityPrivilege](ID,DescriptionAR,DescriptionEN,SystemMenuCode,PrivilegeType) VALUES (4412,N'حفظ أسس الاحتساب',N'Delete Payroll Basis',1239,2) ----------------------------------------- End 2026-01-14 --------------------- ----------------------------------------- Ali 2026-01-14 --------------------- UPDATE MilitaryData SET CityCode = NULL WHERE CityCode = 0; Go delete from [MilitaryData] where [EmployeeCode] not in(select [EmployeeCode] from Employees) Go UPDATE [MilitaryData] SET [MilitaryCode] = NULL WHERE [MilitaryCode] = 0; Go ----------------------------------------- End 2026-01-14 --------------------- ----------------------------------------- Ali 2026-01-19 --------------------- IF NOT EXISTS (SELECT 1 FROM sys.columns WHERE name = 'AllocationByLocation' AND object_id = OBJECT_ID('dbo.Basis')) ALTER TABLE dbo.Basis ADD AllocationByLocation bit NULL Go ----------------------------------------- End 2026-01-19 ---------------------