﻿ --------------------------------Hammad 2019-11-4-------------------------
ALTER TABLE dbo.AC_Authority ADD
	ShortName nvarchar(50) NULL
GO

INSERT [dbo].[UserType] ([UserTypeCode], [Name], [Arabic_Name]) VALUES (19, N'Other Section Manager', N'مدير  قسم  آخر')
INSERT [dbo].[UserType] ([UserTypeCode], [Name], [Arabic_Name]) VALUES (20, N'Other Department Manager', N' مدير  إدارة  آخر')
INSERT [dbo].[UserType] ([UserTypeCode], [Name], [Arabic_Name]) VALUES (21, N'Other Division Manager', N' مدير  قطاع  آخر')
Go


CREATE TABLE [dbo].[WF_SendEmailSetup](
	[SendEmailSetupCode] [int] IDENTITY(1,1) NOT NULL,
	[RequestTypeId] [int] NOT NULL,
	[ToUserAfterHourCount] [float] NOT NULL,
	[ToUpLevelUserAfterHourCount] [float] NOT NULL,
	[ToUpLevelUserTypeCode] [int] NOT NULL,
 CONSTRAINT [PK_WF_SendEmailSetup] PRIMARY KEY CLUSTERED 
(
	[SendEmailSetupCode] 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

CREATE TABLE [dbo].[WF_SendEmailBcc](
	[SendEmailBccCode] [int] IDENTITY(1,1) NOT NULL,
	[SendEmailSetupCode] [int] NOT NULL,
	[UserTypeCode] [int] NOT NULL,
 CONSTRAINT [PK_WF_SendEmailBcc] PRIMARY KEY CLUSTERED 
(
	[SendEmailBccCode] 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 [dbo].[WF_SendEmailBcc]  WITH CHECK ADD  CONSTRAINT [FK_WF_SendEmailBcc_WF_SendEmailSetup] FOREIGN KEY([SendEmailSetupCode])
REFERENCES [dbo].[WF_SendEmailSetup] ([SendEmailSetupCode])
GO

ALTER TABLE [dbo].[WF_SendEmailBcc] CHECK CONSTRAINT [FK_WF_SendEmailBcc_WF_SendEmailSetup]
GO

CREATE TABLE [dbo].[WF_EmailNotification](
	[EmailNotificationCode] [int] IDENTITY(1,1) NOT NULL,
	[RequestTypeCode] [int] NOT NULL,
	[RequestCode] [int] NOT NULL,
	[RequestSignatureCode] [int] NOT NULL,
	[NotificationDate] [datetime] NOT NULL,
	[SendCount] [int] NOT NULL,
 CONSTRAINT [PK_WF_EmailNotification] PRIMARY KEY CLUSTERED 
(
	[EmailNotificationCode] 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


INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType])
 VALUES (3451, N'عرض إعدادات إرسال التنبية', N'View Send Notification Email Setup', 96, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType])
 VALUES (3452, N'حفظ إعدادات إرسال التنبية', N'Save Send Notification Email Setup ', 96, 2)
 Go
 --------------------------------End 2019-11-4----------------------------

  --------------------------------Ali 2019-11-11----------------------------


DROP TABLE  [dbo].[WF_TransferRequest] , [dbo].[WF_MasterRequestsSignature], [dbo].[WF_MasterRequestsCondition],[dbo].[WF_MasterRequests]




SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WF_MasterRequests](
	[MasterRequestCode] [int] IDENTITY(1,1) NOT NULL,
	[RequestTypeCode] [int] NOT NULL,
	[TrackCode] [int] NOT NULL,
	[RequestDate] [datetime] NULL,
	[RequestDateHijri] [nvarchar](10) NULL,
	[MainType] [tinyint] NOT NULL,
	[DepartmentApproval] [tinyint] NOT NULL,
	[Notes] [nvarchar](500) NULL,
	[Canceled] [bit] NULL,
	[CancelRequestNumber] [int] NULL,
	[CreatedBy] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[CreationDateHijri] [nvarchar](10) NOT NULL,
	[ModifiedBy] [int] NULL,
	[ModificationDate] [datetime] NULL,
	[ModificationDateHijri] [nvarchar](10) NULL,
 CONSTRAINT [PK_MasterRequests] PRIMARY KEY CLUSTERED 
(
	[MasterRequestCode] 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 [dbo].[WF_MasterRequests]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequests_SecurityUser] FOREIGN KEY([CreatedBy])
REFERENCES [dbo].[SecurityUser] ([SecurityUserCode])
GO

ALTER TABLE [dbo].[WF_MasterRequests] CHECK CONSTRAINT [FK_WF_MasterRequests_SecurityUser]
GO

ALTER TABLE [dbo].[WF_MasterRequests]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequests_SecurityUser1] FOREIGN KEY([ModifiedBy])
REFERENCES [dbo].[SecurityUser] ([SecurityUserCode])
GO

ALTER TABLE [dbo].[WF_MasterRequests] CHECK CONSTRAINT [FK_WF_MasterRequests_SecurityUser1]
GO

ALTER TABLE [dbo].[WF_MasterRequests]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequests_WF_RequestStatus] FOREIGN KEY([DepartmentApproval])
REFERENCES [dbo].[WF_RequestStatus] ([RequestStatusId])
GO

ALTER TABLE [dbo].[WF_MasterRequests] CHECK CONSTRAINT [FK_WF_MasterRequests_WF_RequestStatus]
GO

ALTER TABLE [dbo].[WF_MasterRequests]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequests_WF_RequestType] FOREIGN KEY([RequestTypeCode])
REFERENCES [dbo].[WF_RequestType] ([RequestTypeId])
GO

ALTER TABLE [dbo].[WF_MasterRequests] CHECK CONSTRAINT [FK_WF_MasterRequests_WF_RequestType]
GO

ALTER TABLE [dbo].[WF_MasterRequests]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequests_WF_Track] FOREIGN KEY([TrackCode])
REFERENCES [dbo].[WF_Track] ([TrackId])
GO

ALTER TABLE [dbo].[WF_MasterRequests] CHECK CONSTRAINT [FK_WF_MasterRequests_WF_Track]
GO




------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WF_MasterRequestsCondition](
	[MasterRequestConditionCode] [int] IDENTITY(1,1) NOT NULL,
	[MasterRequestCode] [int] NOT NULL,
	[PositionConditionCode] [int] NOT NULL,
 CONSTRAINT [PK_WF_RequestCondition] PRIMARY KEY CLUSTERED 
(
	[MasterRequestConditionCode] 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 [dbo].[WF_MasterRequestsCondition]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequestsCondition_PositionCondition] FOREIGN KEY([PositionConditionCode])
REFERENCES [dbo].[PositionCondition] ([PositionConditionCode])
GO

ALTER TABLE [dbo].[WF_MasterRequestsCondition] CHECK CONSTRAINT [FK_WF_MasterRequestsCondition_PositionCondition]
GO

ALTER TABLE [dbo].[WF_MasterRequestsCondition]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequestsCondition_WF_MasterRequests] FOREIGN KEY([MasterRequestCode])
REFERENCES [dbo].[WF_MasterRequests] ([MasterRequestCode])
GO

ALTER TABLE [dbo].[WF_MasterRequestsCondition] CHECK CONSTRAINT [FK_WF_MasterRequestsCondition_WF_MasterRequests]
GO

------------------------------------------------------------------------------------

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WF_MasterRequestsSignature](
	[MasterRequestSignatureCode] [int] IDENTITY(1,1) NOT NULL,
	[MasterRequestCode] [int] NOT NULL,
	[UserCode] [int] NOT NULL,
	[StepNumber] [int] NOT NULL,
	[Approved] [tinyint] NOT NULL,
	[SignatureDate] [datetime] NULL,
	[SignatureDateHijri] [nvarchar](10) NULL,
	[SignatureTime] [nvarchar](10) NULL,
	[SignatureComment] [nvarchar](500) NULL,
	[SignatureCommentInverse] [nvarchar](500) NULL,
	[Turn] [int] NULL,
	[OldUserCode] [int] NULL,
	[ChangedBy] [int] NULL,
	[ChangeDate] [datetime] NULL,
	[UserTypeCode] [int] NULL,
 CONSTRAINT [PK_WF_MasterRequestsSignature] PRIMARY KEY CLUSTERED 
(
	[MasterRequestSignatureCode] 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 [dbo].[WF_MasterRequestsSignature]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequestsSignature_SecurityUser] FOREIGN KEY([UserCode])
REFERENCES [dbo].[SecurityUser] ([SecurityUserCode])
GO

ALTER TABLE [dbo].[WF_MasterRequestsSignature] CHECK CONSTRAINT [FK_WF_MasterRequestsSignature_SecurityUser]
GO

ALTER TABLE [dbo].[WF_MasterRequestsSignature]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequestsSignature_WF_MasterRequests] FOREIGN KEY([MasterRequestCode])
REFERENCES [dbo].[WF_MasterRequests] ([MasterRequestCode])
GO

ALTER TABLE [dbo].[WF_MasterRequestsSignature] CHECK CONSTRAINT [FK_WF_MasterRequestsSignature_WF_MasterRequests]
GO

ALTER TABLE [dbo].[WF_MasterRequestsSignature]  WITH CHECK ADD  CONSTRAINT [FK_WF_MasterRequestsSignature_WF_RequestStatus] FOREIGN KEY([Approved])
REFERENCES [dbo].[WF_RequestStatus] ([RequestStatusId])
GO

ALTER TABLE [dbo].[WF_MasterRequestsSignature] CHECK CONSTRAINT [FK_WF_MasterRequestsSignature_WF_RequestStatus]
GO
----------------------------------------------------------------------------------------------------------------------
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[WF_TransferRequest](
	[MasterRequestCode] [int] NOT NULL,
	[EmployeeCode] [int] NOT NULL,
	[OldLocationCode] [tinyint] NOT NULL,
	[OldPositionCode] [nvarchar](30) NOT NULL,
	[OldDepartmentCode] [nvarchar](4) NOT NULL,
	[OldSectionCode] [nvarchar](6) NOT NULL,
	[NewLocationCode] [tinyint] NOT NULL,
	[NewPositionCode] [nvarchar](30) NOT NULL,
	[NewSectionCode] [nvarchar](6) NOT NULL,
	[NewDepartmentCode] [nvarchar](4) NOT NULL,
 CONSTRAINT [PK_WF_TransferRequest_1] PRIMARY KEY CLUSTERED 
(
	[MasterRequestCode] 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 [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Departments_New] FOREIGN KEY([NewDepartmentCode])
REFERENCES [dbo].[Departments] ([DepartmentCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Departments_New]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Departments_Old] FOREIGN KEY([OldDepartmentCode])
REFERENCES [dbo].[Departments] ([DepartmentCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Departments_Old]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Employees] FOREIGN KEY([EmployeeCode])
REFERENCES [dbo].[Employees] ([EmployeeCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Employees]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Location_New] FOREIGN KEY([NewLocationCode])
REFERENCES [dbo].[Location] ([LocationCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Location_New]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Location_Old] FOREIGN KEY([OldLocationCode])
REFERENCES [dbo].[Location] ([LocationCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Location_Old]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_MasterRequests] FOREIGN KEY([MasterRequestCode])
REFERENCES [dbo].[WF_MasterRequests] ([MasterRequestCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_MasterRequests]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Positions_New] FOREIGN KEY([NewPositionCode])
REFERENCES [dbo].[Positions] ([PositionCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Positions_New]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Positions_Old] FOREIGN KEY([OldPositionCode])
REFERENCES [dbo].[Positions] ([PositionCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Positions_Old]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Sections_New] FOREIGN KEY([NewSectionCode])
REFERENCES [dbo].[Sections] ([SectionCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Sections_New]
GO

ALTER TABLE [dbo].[WF_TransferRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_TransferRequest_Sections_Old] FOREIGN KEY([OldSectionCode])
REFERENCES [dbo].[Sections] ([SectionCode])
GO

ALTER TABLE [dbo].[WF_TransferRequest] CHECK CONSTRAINT [FK_WF_TransferRequest_Sections_Old]
GO



    UPDATE [SecurityPrivilege]
SET [SystemMenuCode]=335
WHERE ID = 3449;

GO
    UPDATE [SecurityPrivilege]
SET [DescriptionAR] = N'عرض سجل الموظف', [DescriptionEN]= 'View Employee History'
WHERE [ID] = 3450;
GO

INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (142, N'Transfer Request Sending', N'ارسال طلب تحويل', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (143, N'Transfer Request Accept', N'الموافقة على طلب تحويل ', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (144, N'Transfer Request Refuse', N'رفض طلب تحويل', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (145, N'Transfer Request Cancellation', N'الغاء طلب تحويل', 0, 1)

GO

INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (142, N'Transfer Request Sending', N'ارسال طلب تحويل', 142, N'ارسال طلب تحويل', N'نرجو الموافقة على طلب التحويل', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (143, N'Transfer Request Accept', N'الموافقة على طلب تحويل', 143, N'اعتماد طلب تحويل', N'<p>تمت الموافقة</p>', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (144, N'Transfer Request Refuse', N'رفض طلب تحويل', 144, N'رفض طلب تحويل', N'تم رفض الطلب', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (145, N'Transfer Request Cancellation', N' إلغاء طلب تحويل', 145, N'إلغاء طلب تحويل', N'تم إلغاء الطلب', 1, NULL)

GO

INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3453, N'ارجاع طلب  تحويل موظف', N'Inverse  Transfer Employee Request', 336, 2)

INSERT [dbo].[WF_Track] ([TrackId], [TrackName], [TrackNameEn], [RequestTypeId], [IsOrdered], [IsBridge], [IsDefault], [VacationCode], [IsOverloadBudget]) VALUES (2047, N'طلب تحويل من قسم', N'Transfer request from department', 25, 0, 0, 0, NULL, 0)

GO
   --------------------------------End 2019-11-11----------------------------11-4----------------------------