﻿--------------------------------------- mostafa  2024-05-07 ------------

CREATE TABLE [dbo].[BankFileDesign](
	[BankFileNumber] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Arabic_Name] [nvarchar](150) NOT NULL,
	[FileType] [tinyint] NULL,
	[FileSort] [tinyint] NULL,
	[ShowSelectDateRequired] [bit] NULL,
	[ShowFoundationsFilter] [bit] NULL,
	[ShowMainAddedFilter] [bit] NULL,
	[ShowBanksFilter] [bit] NULL,
	[FirstLineColumnsCount] [int] NULL,
	[SecondLineColumnsCount] [int] NULL,
	[DetailsColumnsCount] [int] NULL,
	[LastLineColumnsCount] [int] NULL,
	[Closed] [bit] NULL,
	[AddGosiToIncomeAndDeduction] [bit] NULL,
	[BankCondition] [nvarchar](150) NULL,
	[ThirdLineColumnsCount] [int] NULL,
	[Serial] [nvarchar](150) NULL,
	[CreatedBy] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[ModifiedBy] [int] NULL,
	[ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_BankFileDesign] PRIMARY KEY CLUSTERED 
(
	[BankFileNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.BankFileDesign');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.BankFileDesign DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
GO

ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_ShowSelectDateRequired  DEFAULT ((0)) FOR [ShowSelectDateRequired]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_ShowFoundationsFilter DEFAULT ((0)) FOR [ShowFoundationsFilter]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_ShowMainAddedFilter DEFAULT ((0)) FOR [ShowMainAddedFilter]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_Closed DEFAULT ((0)) FOR [Closed]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_AddGosiToIncomeAndDeduction DEFAULT ((0)) FOR [AddGosiToIncomeAndDeduction]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_CreatedBy DEFAULT ((1)) FOR [CreatedBy]
GO									   
ALTER TABLE [dbo].[BankFileDesign] ADD CONSTRAINT DF_BankFileDesign_CreationDate DEFAULT (getdate()) FOR [CreationDate]
GO



DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.Banks');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.Banks DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
GO

BEGIN TRANSACTION
ALTER TABLE dbo.Currencies SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.Banks DROP CONSTRAINT FK_Banks_ExportFileType
COMMIT
GO
BEGIN TRANSACTION
ALTER TABLE dbo.BankFileDesign SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.Banks DROP CONSTRAINT FK_Banks_CurrencyCode
COMMIT
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

CREATE TABLE dbo.Tmp_Banks
	(
	BankCode tinyint NOT NULL IDENTITY (0, 1),
	Name nvarchar(150) NULL,
	Arabic_Name nvarchar(150) NULL,
	BankNet float(53) NULL,
	StringNet nvarchar(250) NULL,
	ArbStringNet nvarchar(250) NULL,
	Branch nvarchar(150) NULL,
	AccountNumber nvarchar(150) NULL,
	CompanyCode nvarchar(50) NULL,
	CurrencyCode tinyint NULL,
	Stop bit NULL,
	ExportFileType int NULL,
	CompanyAddress nvarchar(150) NULL,
	BankAddress nvarchar(150) NULL,
	EmployeesCountDigits int NULL,
	NetSalariesAccount nvarchar(30) NULL,
	TotalAmountFormat nvarchar(150) NULL,
	BankShortName nvarchar(150) NULL,
	AccountNumber2 nvarchar(150) NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_Banks SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT dbo.Tmp_Banks ON

IF EXISTS(SELECT * FROM dbo.Banks)
	 EXEC('INSERT INTO dbo.Tmp_Banks (BankCode, Name, Arabic_Name, BankNet, StringNet, ArbStringNet, Branch, AccountNumber, CompanyCode, CurrencyCode, Stop, ExportFileType, CompanyAddress, BankAddress, EmployeesCountDigits, NetSalariesAccount, TotalAmountFormat, BankShortName, AccountNumber2)
		SELECT BankCode, CONVERT(nvarchar(150), Name), CONVERT(nvarchar(150), Arabic_Name), BankNet, CONVERT(nvarchar(250), StringNet), CONVERT(nvarchar(250), ArbStringNet), Branch, CONVERT(nvarchar(150), AccountNumber), CompanyCode, CurrencyCode, Stop, ExportFileType, CONVERT(nvarchar(150), CompanyAddress), CONVERT(nvarchar(150), BankAddress), EmployeesCountDigits, NetSalariesAccount, CONVERT(nvarchar(150), TotalAmountFormat), BankShortName, AccountNumber2 FROM dbo.Banks WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_Banks OFF

DROP TABLE dbo.Banks

EXECUTE sp_rename N'dbo.Tmp_Banks', N'Banks', 'OBJECT' 

ALTER TABLE dbo.Banks ADD CONSTRAINT
	PK_Banks PRIMARY KEY CLUSTERED 
	(
	BankCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


COMMIT
GO
ALTER TABLE dbo.Banks ADD CONSTRAINT FK_Banks_ExportFileType FOREIGN KEY (ExportFileType) REFERENCES dbo.BankFileDesign(BankFileNumber) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 
GO
ALTER TABLE dbo.Banks ADD CONSTRAINT FK_Banks_CurrencyCode FOREIGN KEY(CurrencyCode) REFERENCES dbo.Currencies(CurrencyCode) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 
GO

ALTER TABLE dbo.Banks ADD
	CreatedBy INT  NULL ,
    CreationDate DATETIME NULL ,
	ModifiedBy int NULL,
	ModificationDate datetime NULL,
	Serial nvarchar(150) NULL,
	SpecialText1 nvarchar(150) NULL,
	SpecialText2 nvarchar(150) NULL,
	SpecialText3 nvarchar(150) NULL,
	OutOfExportBankFile bit NULL 
	GO

ALTER TABLE dbo.Banks ADD CONSTRAINT DF_Banks_CreatedBy DEFAULT ((1)) FOR CreatedBy;
GO									
ALTER TABLE dbo.Banks ADD CONSTRAINT DF_Banks_CreationDate DEFAULT (GETDATE()) FOR CreationDate;
GO									
ALTER TABLE dbo.Banks ADD CONSTRAINT DF_Banks_Stop DEFAULT ((0)) FOR Stop;
GO		
update Banks set Serial=BankCode ,OutOfExportBankFile=0
GO
ALTER TABLE dbo.Banks ADD CONSTRAINT DF_Banks_OutOfExportBankFile DEFAULT ((0)) FOR OutOfExportBankFile;
GO	
update SecurityPrivilegeEndPoints set ApiName =N'Personal' where Id in (1196,1197,1198,1199,1200,1201)

go
UPDATE  Religion SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  HolidaysMap SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Guest SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  PenaltyCategories SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  PenaltiesList SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Cities SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  SubContractGroups SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Contract SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  HealthIDType SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  HealthInsuranceTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Insurer SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  DocumentsTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  InvestigationRelatedTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  MedicalTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Military SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Nationality SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  PromotionReasons SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Restaurants SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  WorkQuitTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  RelativeTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Faculties SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  FineSpecializations SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Grade SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Languages SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Qualification SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Specializations SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE  Universities SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   BankAccountTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   BonusTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   InstallmentTypes SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   MaritalStatus SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   PayrollGroups SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   Currencies SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   TaxSheet SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   Banks SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
UPDATE   Pensions SET Name = CASE WHEN COALESCE(Name, '') = '' OR Name IS NULL THEN Arabic_Name ELSE Name END, Arabic_Name = CASE WHEN COALESCE(Arabic_Name, '') = '' OR Arabic_Name IS NULL THEN Name  ELSE Arabic_Name END;
GO
ALTER TABLE Guest ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE PenaltyCategories ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Cities ALTER COLUMN Arabic_Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Contract ALTER COLUMN Arabic_Name nvarchar(1000) NOT NULL;
GO
ALTER TABLE HealthInsuranceTypes ALTER COLUMN Arabic_Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Insurer ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE DocumentsTypes ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Military ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Nationality ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE PromotionReasons ALTER COLUMN Arabic_Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Faculties ALTER COLUMN Arabic_Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Grade ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Languages ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE MaritalStatus ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Currencies ALTER COLUMN Arabic_Name nvarchar(150) NOT NULL;
GO

ALTER TABLE Guest ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE PenaltyCategories ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Cities ALTER COLUMN Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Contract ALTER COLUMN Name nvarchar(1000) NOT NULL;
GO
ALTER TABLE HealthInsuranceTypes ALTER COLUMN Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Insurer ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE DocumentsTypes ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Military ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Nationality ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE PromotionReasons ALTER COLUMN Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Faculties ALTER COLUMN Name nvarchar(250) NOT NULL;
GO
ALTER TABLE Grade ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Languages ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE MaritalStatus ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
ALTER TABLE Currencies ALTER COLUMN Name nvarchar(150) NOT NULL;
GO
UPDATE Religion SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE HolidaysMap SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Guest SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE PenaltyCategories SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE PenaltiesList SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Cities SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE SubContractGroups SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Contract SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE HealthInsuranceTypes SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Insurer SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE DocumentsTypes SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Military SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Nationality SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE PromotionReasons SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Faculties SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Grade SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Languages SET Audit_CreatedBy = 1 WHERE Audit_CreatedBy IS NULL OR Audit_CreatedBy = 0;
GO
UPDATE  MaritalStatus SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE  Currencies SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE  Banks SET CreatedBy = 1 WHERE CreatedBy IS NULL OR CreatedBy = 0;
GO
UPDATE Religion SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE HolidaysMap SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Guest SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE PenaltyCategories SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE PenaltiesList SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Cities SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE SubContractGroups SET MDT = GETDATE() WHERE MDT IS NULL;
GO
UPDATE Contract SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE HealthInsuranceTypes SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Insurer SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE DocumentsTypes SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Military SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Nationality SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE PromotionReasons SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE RelativeTypes SET MDT = GETDATE() WHERE MDT IS NULL;
GO
UPDATE Faculties SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Grade SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE Languages SET Audit_CreationDate = GETDATE() WHERE Audit_CreationDate IS NULL;
GO
UPDATE  MaritalStatus SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE  Currencies SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
UPDATE  Banks SET CreationDate = GETDATE() WHERE CreationDate IS NULL;
GO
ALTER TABLE Guest ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Guest ADD CONSTRAINT DF_Guest_CreatedBy DEFAULT 1 FOR CreatedBy;
GO
ALTER TABLE Guest ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Guest ADD CONSTRAINT DF_Guest_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE PenaltyCategories ALTER COLUMN CreatedBy INT NOT NULL;
GO
ALTER TABLE PenaltyCategories ADD CONSTRAINT DF_PenaltyCategories_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE PenaltyCategories ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE PenaltyCategories ADD CONSTRAINT DF_PenaltyCategories_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Cities ALTER COLUMN CreatedBy INT NOT NULL;
GO
ALTER TABLE Cities ADD CONSTRAINT DF_Cities_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Cities ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Cities ADD CONSTRAINT DF_Cities_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Contract ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Contract ADD CONSTRAINT DF_Contract_CreatedBy DEFAULT 1 FOR CreatedBy;  
GO
ALTER TABLE Contract ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Contract ADD CONSTRAINT DF_Contract_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE HealthInsuranceTypes ALTER COLUMN CreatedBy INT NOT NULL;
GO
ALTER TABLE HealthInsuranceTypes ADD CONSTRAINT DF_HealthInsuranceTypes_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE HealthInsuranceTypes ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE HealthInsuranceTypes ADD CONSTRAINT DF_HealthInsuranceTypes_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Insurer ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Insurer ADD CONSTRAINT DF_Insurer_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Insurer ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Insurer ADD CONSTRAINT DF_Insurer_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE DocumentsTypes ALTER COLUMN CreatedBy INT NOT NULL;
GO
ALTER TABLE DocumentsTypes ADD CONSTRAINT DF_DocumentsTypes_CreatedBy DEFAULT 1 FOR CreatedBy;  
GO
ALTER TABLE DocumentsTypes ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE DocumentsTypes ADD CONSTRAINT DF_DocumentsTypes_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Military ALTER COLUMN CreatedBy INT NOT NULL; ALTER TABLE Military ADD CONSTRAINT DF_Military_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Military ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Military ADD CONSTRAINT DF_Military_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Nationality ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Nationality ADD CONSTRAINT DF_Nationality_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Nationality ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Nationality ADD CONSTRAINT DF_Nationality_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE PromotionReasons ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE PromotionReasons ADD CONSTRAINT DF_PromotionReasons_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE PromotionReasons ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE PromotionReasons ADD CONSTRAINT DF_PromotionReasons_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Faculties ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Faculties ADD CONSTRAINT DF_Faculties_CreatedBy DEFAULT 1 FOR CreatedBy;  
GO
ALTER TABLE Faculties ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Faculties ADD CONSTRAINT DF_Faculties_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Grade ALTER COLUMN CreatedBy INT NOT NULL;
GO
ALTER TABLE Grade ADD CONSTRAINT DF_Grade_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Grade ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Grade ADD CONSTRAINT DF_Grade_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Languages ALTER COLUMN Audit_CreatedBy INT NOT NULL;
GO
ALTER TABLE Languages ADD CONSTRAINT DF_Languages_Audit_CreatedBy DEFAULT 1 FOR Audit_CreatedBy; 
GO
ALTER TABLE Languages ALTER COLUMN Audit_CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Languages ADD CONSTRAINT DF_Languages_Audit_CreationDate DEFAULT GETDATE() FOR Audit_CreationDate;
GO
ALTER TABLE MaritalStatus ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE MaritalStatus ADD CONSTRAINT DF_MaritalStatus_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE MaritalStatus ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE MaritalStatus ADD CONSTRAINT DF_MaritalStatus_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO
ALTER TABLE Currencies ALTER COLUMN CreatedBy INT NOT NULL; 
GO
ALTER TABLE Currencies ADD CONSTRAINT DF_Currencies_CreatedBy DEFAULT 1 FOR CreatedBy; 
GO
ALTER TABLE Currencies ALTER COLUMN CreationDate DATETIME NOT NULL;
GO
ALTER TABLE Currencies ADD CONSTRAINT DF_Currencies_CreationDate DEFAULT GETDATE() FOR CreationDate;
GO


DROP TABLE Tmp_PenaltiesList;
GO
DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.PenaltiesList');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.PenaltiesList DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
GO


ALTER TABLE dbo.WF_PenaltyRequest
	DROP CONSTRAINT FK_WF_PenaltyRequest_PenaltiesList
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


CREATE TABLE dbo.Tmp_PenaltiesList
	(
	PenaltyCode smallint NOT NULL IDENTITY (0, 1),
	Name nvarchar(150) NULL,
	Arabic_Name nvarchar(150) 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,
	PenaltiesListSerial nvarchar(150) NULL,
	DropMonths int NULL,
	IgnoreDelayedDays bit NULL,
	YealyEvaluationPoint float(53) NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_PenaltiesList SET (LOCK_ESCALATION = TABLE)

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, PenaltiesListSerial, DropMonths, IgnoreDelayedDays, YealyEvaluationPoint)
		SELECT PenaltyCode, CONVERT(nvarchar(150), Name), CONVERT(nvarchar(150), Arabic_Name), Amount1, Amount2, Amount3, Amount4, DescriptionEnglish1, DescriptionEnglish2, DescriptionEnglish3, DescriptionEnglish4, DescriptionArabic1, DescriptionArabic2, DescriptionArabic3, DescriptionArabic4, AmountValue, Closed, CloseModification, ItemNumber, PenaltyCategoryCode, ShowInRequest, CreatedBy, CreationDate, ModifiedBy, ModificationDate, PenaltiesListSerial, DropMonths, IgnoreDelayedDays, YealyEvaluationPoint 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]


COMMIT

BEGIN TRANSACTION

ALTER TABLE dbo.WF_PenaltyRequest ADD CONSTRAINT
	FK_WF_PenaltyRequest_PenaltiesList FOREIGN KEY
	(
	PenaltyCode
	) REFERENCES dbo.PenaltiesList
	(
	PenaltyCode
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	

ALTER TABLE dbo.WF_PenaltyRequest SET (LOCK_ESCALATION = TABLE)

COMMIT

GO

ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__Amount1 DEFAULT ((0)) FOR Amount1
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__Amount2 DEFAULT ((0)) FOR Amount2
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__Amount3 DEFAULT ((0)) FOR Amount3
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__Amount4 DEFAULT ((0)) FOR Amount4
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__AmountValue DEFAULT ((0)) FOR AmountValue
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__Closed DEFAULT ((0)) FOR Closed
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__CloseModification DEFAULT ((0)) FOR CloseModification
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__ShowInRequest DEFAULT ((0)) FOR ShowInRequest
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__CreatedBy DEFAULT 1 FOR CreatedBy
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__CreationDate DEFAULT (getdate()) FOR CreationDate
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__IgnoreDelayedDays DEFAULT ((0)) FOR IgnoreDelayedDays
GO
ALTER TABLE dbo.PenaltiesList ADD CONSTRAINT
	DF__PenaltiesList__YealyEvaluationPoint DEFAULT ((0)) FOR YealyEvaluationPoint
GO


DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.Religio');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.Religio DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
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

CREATE TABLE dbo.Tmp_Religion
	(
	ReligionCode tinyint NOT NULL IDENTITY (0, 1),
	Name nvarchar(150) NOT NULL,
	Arabic_Name nvarchar(150) NOT NULL,
	MDT datetime NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	ModifiedBy int NULL,
	ModificationDate datetime NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_Religion SET (LOCK_ESCALATION = TABLE)


SET IDENTITY_INSERT dbo.Tmp_Religion ON

IF EXISTS(SELECT * FROM dbo.Religion)
	 EXEC('INSERT INTO dbo.Tmp_Religion (ReligionCode, Name, Arabic_Name, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate)
		SELECT ReligionCode, Name, Arabic_Name, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate FROM dbo.Religion WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_Religion OFF

DROP TABLE dbo.Religion

EXECUTE sp_rename N'dbo.Tmp_Religion', N'Religion', 'OBJECT' 

ALTER TABLE dbo.Religion ADD CONSTRAINT
	PK_Religion PRIMARY KEY CLUSTERED 
	(
	ReligionCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


COMMIT
GO
ALTER TABLE dbo.Religion ADD CONSTRAINT
	DF_Religion_MDT DEFAULT (GETDATE()) FOR MDT
GO
ALTER TABLE dbo.Religion ADD CONSTRAINT
	DF_Religion_CreatedBy DEFAULT ((1)) FOR CreatedBy
GO
ALTER TABLE dbo.Religion ADD CONSTRAINT
	DF_Religion_CreationDate DEFAULT (getdate()) FOR CreationDate
GO
DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.SubContractGroups');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.SubContractGroups DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
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


CREATE TABLE dbo.Tmp_SubContractGroups
	(
	SubContractGroupCode int NOT NULL IDENTITY (0, 1),
	Name nvarchar(150) NOT NULL,
	Arabic_Name nvarchar(150) NOT NULL,
	Closed bit NULL,
	MDT datetime NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	ModifiedBy int NULL,
	ModificationDate datetime NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_SubContractGroups SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT dbo.Tmp_SubContractGroups ON

IF EXISTS(SELECT * FROM dbo.SubContractGroups)
	 EXEC('INSERT INTO dbo.Tmp_SubContractGroups (SubContractGroupCode, Name, Arabic_Name, Closed, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate)
		SELECT SubContractGroupCode, Name, Arabic_Name, Closed, MDT, CreatedBy, CreationDate, ModifiedBy, ModificationDate FROM dbo.SubContractGroups WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_SubContractGroups OFF

DROP TABLE dbo.SubContractGroups

EXECUTE sp_rename N'dbo.Tmp_SubContractGroups', N'SubContractGroups', 'OBJECT' 

ALTER TABLE dbo.SubContractGroups ADD CONSTRAINT
	PK_SubContractGroups_SubContractGroupCode PRIMARY KEY CLUSTERED 
	(
	SubContractGroupCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


COMMIT

GO
ALTER TABLE dbo.SubContractGroups ADD CONSTRAINT
	DF_SubContractGroups_Closed DEFAULT ((0)) FOR Closed
GO
ALTER TABLE dbo.SubContractGroups ADD CONSTRAINT
	DF_SubContractGroups_MDT DEFAULT (GETDATE()) FOR MDT
GO
ALTER TABLE dbo.SubContractGroups ADD CONSTRAINT
	DF_SubContractGroups_CreatedBy DEFAULT ((1)) FOR CreatedBy
GO
ALTER TABLE dbo.SubContractGroups ADD CONSTRAINT
	DF_SubContractGroups_CreationDate DEFAULT (GETDATE()) FOR CreationDate
GO
delete SecurityPrivilegeEndPointsDetails where SecurityPrivilegeEndPointId in(921,922,923,924,925,926)
Go
delete SecurityPrivilegeEndPoints  where id in(921,922,923,924,925,926)
GO
delete SecurityPrivilegeEndPointsDetails where SecurityPrivilegeEndPointId in (1024,1025,1026,1027,1028,1029)
GO
Delete SecurityPrivilegeEndPoints where id in (1024,1025,1026,1027,1028,1029)
GO
Delete SecurityPrivilegeActions where SecurityPrivilegeID in (4066,4067,4068,4069,4070,4071)
GO
Delete SecurityPrivilege where ID in (4066,4067,4068,4069,4070,4071)
GO
delete SecurityGroupDetail where PrivilegeID in (4096,4097,4098,4099,4100,4101)
GO
delete SecurityUserDetail where PrivilegeID in (4096,4097,4098,4099,4100,4101)
GO
delete SecurityPrivilegeEndPointsDetails where SecurityPrivilegeEndPointId in (1054,1055,1056,1057,1058,1059)
GO
delete SecurityPrivilegeEndPoints where ID in (1054,1055,1056,1057,1058,1059)
GO
delete SecurityPrivilegeActions where ID in (1579,1580,1581,1582,1583,1584)
GO
delete SecurityPrivilege where SystemMenuCode=1055
GO
delete SystemMenuModule where SystemMenuCode=1055
GO
delete SystemMenu where SystemMenuCode=1055
GO
delete SecurityGroupDetail where PrivilegeID in (4066,4067,4068,4069,4070,4071)
GO
delete SecurityUserDetail where PrivilegeID in (4066,4067,4068,4069,4070,4071)
GO
delete SecurityPrivilegeEndPointsDetails where SecurityPrivilegeEndPointId in (1024,1025,1026,1027,1028,1029)
GO
delete SecurityPrivilegeEndPoints where ID in (1024,1025,1026,1027,1028,1029)
GO
delete SecurityPrivilegeActions where ID in (1549,1550,1551,1552,1553,1554)
GO
delete SecurityPrivilege where id in (4066,4067,4068,4069,4070,4071)
GO
delete SystemMenuModule where SystemMenuCode=1026 and	AppModuleCode=22
GO
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1073,23)
go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1083,N'Fine Specialization',N'التخصص الدقيق',1063,N'/Personal/FineSpecialization',N'Index','fa fa-thumb-tack',N'get',1,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1083,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4182,N'التخصص الدقيق',N'Fine Specialization',1083,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4183,N'عرض التخصص الدقيق',N'View Fine Specialization',1083,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4184,N'إضافة التخصص الدقيق',N'Add Fine Specialization',1083,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4185,N'تعديل التخصص الدقيق',N'Edit Fine Specialization',1083,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4186,N'حذف التخصص الدقيق',N'Delete FineSpecialization',1083,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4187,N'حفظ التخصص الدقيق',N'Save Fine Specialization',1083,2)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1791,N'Personal',N'FineSpecialization',N'Index',0,N'get',4182)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1792,N'Personal',N'FineSpecialization',N'FineSpecialization_Read',0,N'post',4183)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1793,N'Personal',N'FineSpecialization',N'Save',1,N'get',4184)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1794,N'Personal',N'FineSpecialization',N'Save',2,N'get',4185)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1795,N'Personal',N'FineSpecialization',N'Delete',0,N'post',4186)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1796,N'Personal',N'FineSpecialization',N'Save',0,N'post',4187)
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1202,NULL, NULL,N'FineSpecialization',N'GetFineSpecializationSearch',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1203,NULL, NULL,N'FineSpecialization',N'GetFineSpecialization',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1204,NULL, NULL,N'FineSpecialization',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1205,NULL, NULL,N'FineSpecialization',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1206,NULL, NULL,N'FineSpecialization',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1207,NULL, NULL,N'FineSpecialization',N'Get',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1477,1202,4183, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1478,1203,4183, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1479,1204,4187, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1480,1205,4187, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1481,1206,4186, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1482,1207,4185, NULL)
Go
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1084,N'Health ID Type',N'انواع البطاقة الصحية',1062,N'/Personal/HealthIDType',N'Index','fa fa-thumb-tack',N'get',11,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1084,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4188,N'انواع البطاقة الصحية',N'Health ID Type',1084,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4189,N'عرض انواع البطاقة الصحية',N'View Health ID Type',1084,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4190,N'إضافة انواع البطاقة الصحية',N'Add Health ID Type',1084,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4191,N'تعديل انواع البطاقة الصحية',N'Edit Health ID Type',1084,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4192,N'حذف انواع البطاقة الصحية',N'Delete Health ID Type',1084,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4193,N'حفظ انواع البطاقة الصحية',N'Save Health ID Type',1084,2)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1797,N'Personal',N'HealthIDType',N'Index',0,N'get',4188)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1798,N'Personal',N'HealthIDType',N'HealthIDType_Read',0,N'post',4189)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1799,N'Personal',N'HealthIDType',N'Save',1,N'get',4190)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1800,N'Personal',N'HealthIDType',N'Save',2,N'get',4191)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1801,N'Personal',N'HealthIDType',N'Delete',0,N'post',4192)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1802,N'Personal',N'HealthIDType',N'Save',0,N'post',4193)
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1214,NULL, NULL,N'HealthIDType',N'GetHealthIDTypeSearch',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1215,NULL, NULL,N'HealthIDType',N'GetHealthIDType',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1216,NULL, NULL,N'HealthIDType',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1217,NULL, NULL,N'HealthIDType',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1218,NULL, NULL,N'HealthIDType',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1219,NULL, NULL,N'HealthIDType',N'Get',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1498,1214,4189, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1499,1215,4189, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1500,1216,4193, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1501,1217,4193, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1502,1218,4192, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1503,1219,4191, NULL)
Go

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1055,N'Promotion Reason',N'اسباب الترقية',1062,N'/Personal/PromotionReason',N'Index','fa fa-thumb-tack',N'get',12,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1055,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4096,N'اسباب الترقية',N'Promotion Reason',1055,1)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4097,N'عرض اسباب الترقية',N'View Promotion Reason',1055,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4098,N'إضافة اسباب الترقية',N'Add Promotion Reason',1055,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4099,N'تعديل اسباب الترقية',N'Edit Promotion Reason',1055,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4100,N'حذف اسباب الترقية',N'Delete Promotion Reason',1055,2)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4101,N'حفظ اسباب الترقية',N'Save Promotion Reason',1055,2)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1579,N'Personal',N'PromotionReason',N'Index',0,N'get',4096)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1580,N'Personal',N'PromotionReason',N'PromotionReason_Read',0,N'post',4097)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1581,N'Personal',N'PromotionReason',N'Save',1,N'get',4098)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1582,N'Personal',N'PromotionReason',N'Save',2,N'get',4099)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1583,N'Personal',N'PromotionReason',N'Delete',0,N'post',4100)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1584,N'Personal',N'PromotionReason',N'Save',0,N'post',4101)
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1054,NULL, NULL,N'PromotionReason',N'GetPromotionReasonSearch',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1055,NULL, NULL,N'PromotionReason',N'GetPromotionReason',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1056,NULL, NULL,N'PromotionReason',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1057,NULL, NULL,N'PromotionReason',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1058,NULL, NULL,N'PromotionReason',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1059,NULL, NULL,N'PromotionReason',N'Get',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1332,1054,4097, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1333,1055,4097, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1334,1056,4101, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1335,1057,4101, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1336,1058,4100, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1337,1059,4099, NULL)
Go
UPDATE HolidaysMap SET OnlyReligionCode = NULL WHERE OnlyReligionCode = 0;
GO
DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.HolidaysMap');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.HolidaysMap DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
GO

BEGIN TRANSACTION
ALTER TABLE dbo.HolidaysMap SET (LOCK_ESCALATION = TABLE)
ALTER TABLE dbo.HolidaysMap DROP CONSTRAINT FK_HolidaysMap_Religion
COMMIT
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.Religion SET (LOCK_ESCALATION = TABLE)

COMMIT
BEGIN TRANSACTION

CREATE TABLE dbo.Tmp_HolidaysMap
	(
	DayDate datetime NULL,
	Name nvarchar(150) NOT NULL,
	Arabic_Name nvarchar(150) NOT NULL,
	Type tinyint NULL,
	Serial bigint NOT NULL IDENTITY (1, 1),
	MyYear int NULL,
	OnlyReligionCode tinyint NULL,
	DayDateHijri nvarchar(10) NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime NOT NULL,
	ModifiedBy int NULL,
	ModificationDate datetime NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_HolidaysMap SET (LOCK_ESCALATION = TABLE)


SET IDENTITY_INSERT dbo.Tmp_HolidaysMap ON

IF EXISTS(SELECT * FROM dbo.HolidaysMap)
	 EXEC('INSERT INTO dbo.Tmp_HolidaysMap (DayDate, Name, Arabic_Name, Type, Serial, MyYear, OnlyReligionCode, DayDateHijri, CreatedBy, CreationDate, ModifiedBy, ModificationDate)
		SELECT DayDate, Name, Arabic_Name, Type, Serial, MyYear, OnlyReligionCode, DayDateHijri, CreatedBy, CreationDate, ModifiedBy, ModificationDate FROM dbo.HolidaysMap WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_HolidaysMap OFF

DROP TABLE dbo.HolidaysMap

EXECUTE sp_rename N'dbo.Tmp_HolidaysMap', N'HolidaysMap', 'OBJECT' 

ALTER TABLE dbo.HolidaysMap ADD CONSTRAINT
	PK__HolidaysMap PRIMARY KEY CLUSTERED 
	(
	Serial
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


COMMIT

GO
BEGIN TRANSACTION
ALTER TABLE dbo.HolidaysMap ADD CONSTRAINT
	FK_HolidaysMap_Religion FOREIGN KEY
	(
	OnlyReligionCode
	) REFERENCES dbo.Religion
	(
	ReligionCode
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
COMMIT
GO

ALTER TABLE dbo.HolidaysMap ADD CONSTRAINT
	DF__Holida__Type DEFAULT ((1)) FOR Type
GO
ALTER TABLE dbo.HolidaysMap ADD CONSTRAINT
	DF__Holida__CreatedBy DEFAULT ((1)) FOR CreatedBy
GO
ALTER TABLE dbo.HolidaysMap ADD CONSTRAINT
	DF__Holida__CreationDate DEFAULT (getdate()) FOR CreationDate
GO

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(1061,N'Relative Types',N'نوع القرابة',1023,N'/Personal/RelativeTypes',N'Index','fa fa-thumb-tack',N'get',16,1,1)
Go
INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(1061,22)
Go
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4132,N'نوع القرابة',N'Relative Types',1061,1)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4133,N'عرض نوع القرابة',N'View Relative Types',1061,2)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4134,N'إضافة نوع القرابة',N'Add Relative Types',1061,2)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4135,N'تعديل نوع القرابة',N'Edit Relative Types',1061,2)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4136,N'حذف نوع القرابة',N'Delete Relative Types',1061,2)
GO
INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(4137,N'حفظ نوع القرابة',N'Save Relative Types',1061,2)
Go
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1630,N'Personal',N'RelativeTypes',N'Index',0,N'get',4132)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1631,N'Personal',N'RelativeTypes',N'RelativeTypes_Read',0,N'post',4133)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1632,N'Personal',N'RelativeTypes',N'Save',1,N'get',4134)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1633,N'Personal',N'RelativeTypes',N'Save',2,N'get',4135)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1634,N'Personal',N'RelativeTypes',N'Delete',0,N'post',4136)
GO
INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1635,N'Personal',N'RelativeTypes',N'Save',0,N'post',4137)
GO
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1091,NULL, NULL,N'RelativeTypes',N'GetRelativeTypesSearch',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1092,NULL, NULL,N'RelativeTypes',N'GetRelativeTypes',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1093,NULL, NULL,N'RelativeTypes',N'Put',N'Put')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1094,NULL, NULL,N'RelativeTypes',N'post',N'post')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1095,NULL, NULL,N'RelativeTypes',N'Delete',N'Delete')
Go
INSERT [dbo].[SecurityPrivilegeEndPoints] ([Id], [ApiName], [AreaName], [ControllerName], [ActionName], [HTTPMethod]) VALUES(1096,NULL, NULL,N'RelativeTypes',N'Get',N'Get')
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1368,1091,4133, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1369,1092,4133, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1370,1093,4137, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1371,1094,4137, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1372,1095,4136, NULL)
Go
INSERT [dbo].[SecurityPrivilegeEndPointsDetails] ([Id], [SecurityPrivilegeEndPointId], [SecurityPrivilegeId], [RequestTypeId]) VALUES(1373,1096,4135, NULL)
Go

DECLARE @ConstraintName NVARCHAR(MAX);
DECLARE @Sql NVARCHAR(MAX);

DECLARE cur_constraints CURSOR FOR
SELECT name
FROM sys.default_constraints
WHERE parent_object_id = OBJECT_ID('dbo.RelativeTypes');

OPEN cur_constraints;
FETCH NEXT FROM cur_constraints INTO @ConstraintName;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        SET @Sql = 'ALTER TABLE dbo.RelativeTypes DROP CONSTRAINT ' + QUOTENAME(@ConstraintName);
        EXEC sp_executesql @Sql;
    END TRY
    BEGIN CATCH
    END CATCH

    FETCH NEXT FROM cur_constraints INTO @ConstraintName;
END

CLOSE cur_constraints;
DEALLOCATE cur_constraints;
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

CREATE TABLE dbo.Tmp_RelativeTypes
	(
	RelativeTypeCode int NOT NULL IDENTITY (0, 1),
	Name nvarchar(150) NOT NULL,
	Arabic_Name nvarchar(150) NOT NULL,
	MDT datetime NULL,
	Serial nvarchar(150) NULL,
	CreatedBy int  NULL,
	CreationDate datetime  NULL,
	ModifiedBy int NULL ,
	ModificationDate datetime NULL,
	Closed bit NULL
	)  ON [PRIMARY]

ALTER TABLE dbo.Tmp_RelativeTypes SET (LOCK_ESCALATION = TABLE)

SET IDENTITY_INSERT dbo.Tmp_RelativeTypes ON

IF EXISTS(SELECT * FROM dbo.RelativeTypes)
	 EXEC('INSERT INTO dbo.Tmp_RelativeTypes (RelativeTypeCode, Name, Arabic_Name, MDT)
		SELECT RelativeTypeCode, CONVERT(nvarchar(150), Name), CONVERT(nvarchar(150), Arabic_Name), MDT FROM dbo.RelativeTypes WITH (HOLDLOCK TABLOCKX)')

SET IDENTITY_INSERT dbo.Tmp_RelativeTypes OFF

DROP TABLE dbo.RelativeTypes

EXECUTE sp_rename N'dbo.Tmp_RelativeTypes', N'RelativeTypes', 'OBJECT' 

ALTER TABLE dbo.RelativeTypes ADD CONSTRAINT
	PK_RelativeTypes PRIMARY KEY CLUSTERED 
	(
	RelativeTypeCode
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


COMMIT

GO
ALTER TABLE dbo.RelativeTypes ADD CONSTRAINT
	DF_RelativeTypes_MDT DEFAULT (getdate()) FOR MDT
GO
ALTER TABLE dbo.RelativeTypes ADD CONSTRAINT
	DF_RelativeTypes_CreatedBy DEFAULT ((1)) FOR CreatedBy
GO
ALTER TABLE dbo.RelativeTypes ADD CONSTRAINT
	DF_RelativeTypes_CreationDate DEFAULT (getdate()) FOR CreationDate
GO
ALTER TABLE dbo.RelativeTypes ADD CONSTRAINT
	DF_RelativeTypes_Closed DEFAULT ((0)) FOR Closed
GO

update RelativeTypes set Serial=RelativeTypeCode,CreatedBy=1 ,CreationDate=GETDATE()

GO
update SecurityPrivilege set SystemMenuCode=1063  where ID=4150
GO
update  SecurityPrivilege set PrivilegeType=1  where id=4150 
GO
update   [SystemMenu] set [ParentID]=1062  where SystemMenuCode=1061 and [ParentID]=1023 
GO
update SecurityPrivilege set SystemMenuCode=1063 where ID =4150
GO
update SystemMenu set parentid=1023 where SystemMenuCode =1063
GO
update SystemMenu set ControllerName=NULL ,ActionName=NULL,HTTPMethod=NULL where SystemMenuCode =1023
GO
update SecurityPrivilege set SystemMenuCode=1063  where ID=4150
GO
update  SecurityPrivilege set PrivilegeType=1  where id=4150 
GO
update Military set serial =Militarycode
GO
CREATE SCHEMA Acc;


GO
CREATE TABLE [Acc].[AccountsChart](
	[AccountsChartCode] [nvarchar](30) NOT NULL,
	[SerialNo] [nvarchar](100) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Arabic_Name] [nvarchar](150) NOT NULL,
	[ParentId] [nvarchar](30) NULL,
	[FollowListCode] [int] NOT NULL,
	[CostCenterStatusCode] [int] NOT NULL,
	[CostCenterChartCode] [int] NULL,
	[IsVisible] [bit] NOT NULL,
	[Audit_CreatedBy] [int] NOT NULL,
	[Audit_CreationDate] [datetime] NOT NULL,
	[Audit_ModifiedBy] [int] NULL,
	[Audit_ModificationDate] [datetime] NULL,
	[IsMain] [bit] NOT NULL,
	[IsRelatedToTaxes] [bit] NOT NULL,
 CONSTRAINT [PK_AccountsChart] PRIMARY KEY CLUSTERED 
(
	[AccountsChartCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Acc].[CostCenterChart](
	[CostCenterChartCode] [int] IDENTITY(1,1) NOT NULL,
	[SerialNo] [nvarchar](100) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Arabic_Name] [nvarchar](150) NOT NULL,
	[ParentId] [int] NULL,
	[IsVisible] [bit] NOT NULL,
	[Audit_CreatedBy] [int] NOT NULL,
	[Audit_CreationDate] [datetime] NOT NULL,
	[Audit_ModifiedBy] [int] NULL,
	[Audit_ModificationDate] [datetime] NULL,
	[IsMain] [bit] NOT NULL,
 CONSTRAINT [PK_CostCenterChart] PRIMARY KEY CLUSTERED 
(
	[CostCenterChartCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Acc].[CostCenterStatus](
	[CostCenterStatusCode] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Arabic_Name] [nvarchar](150) NOT NULL,
	[Audit_CreatedBy] [int] NOT NULL,
	[Audit_CreationDate] [datetime] NOT NULL,
	[Audit_ModifiedBy] [int] NULL,
	[Audit_ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_CostCenterStatus] PRIMARY KEY CLUSTERED 
(
	[CostCenterStatusCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [Acc].[FollowList](
	[FollowListCode] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](150) NOT NULL,
	[Arabic_Name] [nvarchar](150) NOT NULL,
	[PostToNextYear] [bit] NOT NULL,
	[Audit_CreatedBy] [int] NOT NULL,
	[Audit_CreationDate] [datetime] NOT NULL,
	[Audit_ModifiedBy] [int] NULL,
	[Audit_ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_FollowList] PRIMARY KEY CLUSTERED 
(
	[FollowListCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [Acc].[AccountsChart] ADD  CONSTRAINT [DF__AccountsC__IsMai]  DEFAULT (CONVERT([bit],(0))) FOR [IsMain]
GO
ALTER TABLE [Acc].[AccountsChart] ADD  CONSTRAINT [DF__AccountsC__IsRel]  DEFAULT (CONVERT([bit],(0))) FOR [IsRelatedToTaxes]
GO
ALTER TABLE [Acc].[CostCenterChart] ADD  DEFAULT (CONVERT([bit],(0))) FOR [IsMain]
GO
ALTER TABLE [Acc].[AccountsChart]  WITH CHECK ADD  CONSTRAINT [FK_AccountsChart_AccountsChart_ParentId] FOREIGN KEY([ParentId])
REFERENCES [Acc].[AccountsChart] ([AccountsChartCode])
GO
ALTER TABLE [Acc].[AccountsChart] CHECK CONSTRAINT [FK_AccountsChart_AccountsChart_ParentId]
GO
ALTER TABLE [Acc].[AccountsChart]  WITH CHECK ADD  CONSTRAINT [FK_AccountsChart_CostCenterChart_CostCenterChartCode] FOREIGN KEY([CostCenterChartCode])
REFERENCES [Acc].[CostCenterChart] ([CostCenterChartCode])
GO
ALTER TABLE [Acc].[AccountsChart] CHECK CONSTRAINT [FK_AccountsChart_CostCenterChart_CostCenterChartCode]
GO
ALTER TABLE [Acc].[AccountsChart]  WITH CHECK ADD  CONSTRAINT [FK_AccountsChart_CostCenterStatus_CostCenterStatusCode] FOREIGN KEY([CostCenterStatusCode])
REFERENCES [Acc].[CostCenterStatus] ([CostCenterStatusCode])
GO
ALTER TABLE [Acc].[AccountsChart] CHECK CONSTRAINT [FK_AccountsChart_CostCenterStatus_CostCenterStatusCode]
GO
ALTER TABLE [Acc].[AccountsChart]  WITH CHECK ADD  CONSTRAINT [FK_AccountsChart_FollowList_FollowListCode] FOREIGN KEY([FollowListCode])
REFERENCES [Acc].[FollowList] ([FollowListCode])
GO
ALTER TABLE [Acc].[AccountsChart] CHECK CONSTRAINT [FK_AccountsChart_FollowList_FollowListCode]
GO
ALTER TABLE [Acc].[CostCenterChart]  WITH CHECK ADD  CONSTRAINT [FK_CostCenterChart_CostCenterChart_ParentId] FOREIGN KEY([ParentId])
REFERENCES [Acc].[CostCenterChart] ([CostCenterChartCode])
GO
ALTER TABLE [Acc].[CostCenterChart] CHECK CONSTRAINT [FK_CostCenterChart_CostCenterChart_ParentId]
GO
--------------------------------------- End  2024-05-07 ------------
