﻿
 -----------------------------------------------Hammad 03-01-2024----------------------
SET IDENTITY_INSERT [dbo].[EmailTemplateType] ON 
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (385, N'Missing Sign In', N'عدم قيام الموظف بتسجيل بصمة الدخول', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (386, N'Missing Sign Out', N'عدم قيام الموظف بتسجيل بصمة الخروج', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (387, N'Late Time', N'التأخير', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (388, N'Short Time', N'التقصير', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (389, N'Time Permissions Out', N'اسنفاذ الموظف عدد الأذون الشخصية', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (390, N'New Hire', N'المعينين الجدد', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (391, N'Expired Probation Period', N'نهاية فترة الاختبار', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (392, N'Resignations', N'الاستقالات', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (393, N'Departmental Changes', N'التغييرات الإدارية', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (394, N'Birth Day', N'أعياد الميلاد', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (395, N'Vacation Balance', N'رصيد الاجازات', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (396, N'Contract Expiration', N'انتهاء التعاقد', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (397, N'Pay Slip', N'كشف الراتب', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (398, N'ID Personal Expiration', N' انتهاء البطاقة الشخصية', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (399, N'Military Certificate', N'شهادة التجنيد', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (400, N'Health Card', N'الشهادة الصحية', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (401, N'Driving License', N'رخصة القيادة', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (402, N'Medical Insurance', N'التأمين الصحي', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (403, N'Rescue Certificate', N'شهادة الانقاذ', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (404, N'Skill Test Expiration', N'شهادة قياس مستوى المهارة', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (405, N'Marine Certificate', N'شهادة البحارة', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (406, N'Criminal Investigation', N'كارنيه البحث الجنائي', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (407, N'Work Permission', N'تصاريح العمل', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (408, N'Residence', N'الاقامة', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (409, N'Retirement', N'المعاش', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (410, N'Anniversary of employee joining the facility', N'الذكرى السنوية للالتحاق الموظف بالمنشأة', 0, 13, 1)
GO
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode],[HaveUserAccount]) VALUES (411, N'Vaction Due', N'أرصدة الإجازات', 0, 13, 1)
GO
SET IDENTITY_INSERT [dbo].[EmailTemplateType] OFF
GO
 -----------------------------------------------End 03-01-2024----------------------
 -----------------------------------------------Ali 03-01-2024----------------------
  

ALTER TABLE dbo.PenaltyCategories SET (LOCK_ESCALATION = TABLE)

COMMIT
BEGIN TRANSACTION

ALTER TABLE dbo.PenaltiesList ADD
	CreatedBy int NOT NULL CONSTRAINT DF_PenaltiesList_CreatedBy DEFAULT ((1)),
	CreationDate datetime NOT NULL CONSTRAINT DF_PenaltiesList_CreationDate DEFAULT (getdate()),
	ModifiedBy int NULL,
	ModificationDate datetime NULL

ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	FK_PenaltiesList_PenaltyCategories FOREIGN KEY
	(
	PenaltyCategoryCode
	) REFERENCES dbo.PenaltyCategories
	(
	PenaltyCategoryCode
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	

ALTER TABLE dbo.PenaltiesList SET (LOCK_ESCALATION = TABLE)
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
	DROP CONSTRAINT FK_PenaltiesList_PenaltyCategories

ALTER TABLE dbo.PenaltyCategories SET (LOCK_ESCALATION = TABLE)

COMMIT
BEGIN TRANSACTION

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_PenaltyCode

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_Amount1

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_Amount2

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_Amount3

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_Closed

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_CloseModification

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_ShowInRequest

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_CreatedBy

ALTER TABLE dbo.PenaltiesList
	DROP CONSTRAINT DF_PenaltiesList_CreationDate

CREATE TABLE dbo.Tmp_PenaltiesList
	(
	PenaltyCode smallint NOT NULL IDENTITY (1, 1),
	Name nvarchar(255) NULL,
	Arabic_Name nvarchar(255) NULL,
	Amount1 float(53) NULL,
	Amount2 float(53) NULL,
	Amount3 float(53) NULL,
	Amount4 float(53) NULL,
	DescriptionEnglish1 nvarchar(100) NULL,
	DescriptionEnglish2 nvarchar(100) NULL,
	DescriptionEnglish3 nvarchar(100) NULL,
	DescriptionEnglish4 nvarchar(100) NULL,
	DescriptionArabic1 nvarchar(100) NULL,
	DescriptionArabic2 nvarchar(100) NULL,
	DescriptionArabic3 nvarchar(100) NULL,
	DescriptionArabic4 nvarchar(100) NULL,
	AmountValue float(53) NULL,
	Closed bit NULL,
	CloseModification bit NULL,
	ItemNumber nvarchar(50) NULL,
	PenaltyCategoryCode int NULL,
	ShowInRequest bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	ModifiedBy int NULL,
	ModificationDate datetime NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_PenaltiesList SET (LOCK_ESCALATION = TABLE)

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_Amount1 DEFAULT ((0)) FOR Amount1

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_Amount2 DEFAULT ((0)) FOR Amount2

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_Amount3 DEFAULT ((0)) FOR Amount3

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_Closed DEFAULT ((0)) FOR Closed

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_CloseModification DEFAULT ((0)) FOR CloseModification

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_ShowInRequest DEFAULT ((1)) FOR ShowInRequest

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_CreatedBy DEFAULT ((1)) FOR CreatedBy

ALTER TABLE dbo.Tmp_PenaltiesList ADD CONSTRAINT
	DF_PenaltiesList_CreationDate DEFAULT (getdate()) FOR CreationDate

SET IDENTITY_INSERT dbo.Tmp_PenaltiesList ON

IF EXISTS(SELECT * FROM dbo.PenaltiesList)
	 EXEC('INSERT INTO dbo.Tmp_PenaltiesList (PenaltyCode, Name, Arabic_Name, Amount1, Amount2, Amount3, Amount4, DescriptionEnglish1, DescriptionEnglish2, DescriptionEnglish3, DescriptionEnglish4, DescriptionArabic1, DescriptionArabic2, DescriptionArabic3, DescriptionArabic4, AmountValue, Closed, CloseModification, ItemNumber, PenaltyCategoryCode, ShowInRequest, CreatedBy, CreationDate, ModifiedBy, ModificationDate)
		SELECT PenaltyCode, Name, Arabic_Name, Amount1, Amount2, Amount3, Amount4, DescriptionEnglish1, DescriptionEnglish2, DescriptionEnglish3, DescriptionEnglish4, DescriptionArabic1, DescriptionArabic2, DescriptionArabic3, DescriptionArabic4, AmountValue, Closed, CloseModification, ItemNumber, PenaltyCategoryCode, ShowInRequest, CreatedBy, CreationDate, ModifiedBy, ModificationDate FROM dbo.PenaltiesList WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_PenaltiesList OFF

DROP TABLE dbo.PenaltiesList

EXECUTE sp_rename N'dbo.Tmp_PenaltiesList', N'PenaltiesList', 'OBJECT' 

ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	PK_PenaltiesList PRIMARY KEY CLUSTERED 
	(
	PenaltyCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	FK_PenaltiesList_PenaltyCategories FOREIGN KEY
	(
	PenaltyCategoryCode
	) REFERENCES dbo.PenaltyCategories
	(
	PenaltyCategoryCode
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
COMMIT
GO
 ALTER TABLE [PenaltiesList]
ADD PenaltiesListSerial nvarchar(150) null ;
Go

Update [PenaltiesList] SET PenaltiesListSerial=PenaltyCode 
Go
  -----------------------------------------------End 03-01-2024----------------------

   