----------------------------Sayed 2020-12-06-----------------------------

CREATE TABLE [dbo].[EmployeeLaborOffice](
	[EmployeeCode] [int] NOT NULL,
	[LaborOfficeExist] [bit] NULL,
	[LaborOfficeNumber] [nvarchar](255) NULL,
	[NationalityStandardCode] [int] NULL,
	[JobCode] [int] NULL,
	[OccupationCode] [int] NULL,
	[EducationStatusCode] [int] NULL,
	[QualifiedCode] [int] NULL,
	[EstablishmentCode] [int] NULL,
	[MDT] [datetime] NULL,
 CONSTRAINT [PK_EmployeeLaborOffice] PRIMARY KEY CLUSTERED 
(
	[EmployeeCode] 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
/****** Object:  Table [dbo].[Establishments]    Script Date: 06/12/2020 12:05:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Establishments](
	[EstablishmentCode] [int] NOT NULL,
	[Name] [nvarchar](255) NULL,
	[Arabic_Name] [nvarchar](255) NULL,
	[EstablishmenNumber] [nvarchar](100) NULL,
	[FoundationCode] [int] NULL,
	[HRResponsibleName] [nvarchar](150) NULL,
	[HRResponsibleJob] [nvarchar](150) NULL,
	[CommercialRegister] [nvarchar](150) NULL,
	[OperatingLicense] [nvarchar](150) NULL,
	[MunicipalLicense] [nvarchar](150) NULL,
	[CivilDefenseLicense] [nvarchar](150) NULL,
	[ClassificationCertificate] [nvarchar](150) NULL,
	[LicensePriceLists] [nvarchar](150) NULL,
	[CommercialRegisterExpireDate] [datetime] NULL,
	[OperatingLicenseExpireDate] [datetime] NULL,
	[MunicipalLicenseExpireDate] [datetime] NULL,
	[CivilDefenseLicenseExpireDate] [datetime] NULL,
	[ClassificationCertificateExpireDate] [datetime] NULL,
	[LicensePriceListsExpireDate] [datetime] NULL,
	[CommercialRegisterExpireDateHijri] [nvarchar](10) NULL,
	[OperatingLicenseExpireDateHijri] [nvarchar](10) NULL,
	[MunicipalLicenseExpireDateHijri] [nvarchar](10) NULL,
	[CivilDefenseLicenseExpireDateHijri] [nvarchar](10) NULL,
	[ClassificationCertificateExpireDateHijri] [nvarchar](10) NULL,
	[LicensePriceListsExpireDateHijri] [nvarchar](10) NULL,
	[Closed] [bit] NULL,
	[CommercialRegisterIssueDate] [datetime] NULL,
	[OperatingLicenseIssueDate] [datetime] NULL,
	[MunicipalLicenseIssueDate] [datetime] NULL,
	[CivilDefenseLicenseIssueDate] [datetime] NULL,
	[ClassificationCertificateIssueDate] [datetime] NULL,
	[LicensePriceListsIssueDate] [datetime] NULL,
	[CommercialRegisterIssueDateHijri] [nvarchar](10) NULL,
	[OperatingLicenseIssueDateHijri] [nvarchar](10) NULL,
	[MunicipalLicenseIssueDateHijri] [nvarchar](10) NULL,
	[CivilDefenseLicenseIssueDateHijri] [nvarchar](10) NULL,
	[ClassificationCertificateIssueDateHijri] [nvarchar](10) NULL,
	[LicensePriceListsIssueDateHijri] [nvarchar](10) NULL,
	[ProxyName] [nvarchar](150) NULL,
	[ProxyTelephone] [nvarchar](150) NULL,
	[MDT] [datetime] NULL,
 CONSTRAINT [PK_Establishments] PRIMARY KEY CLUSTERED 
(
	[EstablishmentCode] 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
/****** Object:  Table [dbo].[Foundations]    Script Date: 06/12/2020 12:05:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Foundations](
	[FoundationCode] [int] NOT NULL,
	[Name] [nvarchar](255) NULL,
	[Arabic_Name] [nvarchar](255) NULL,
	[InteriorMinistryNumber] [nvarchar](100) NULL,
	[LaborMinistryNumber] [nvarchar](100) NULL,
	[BankAccountNumber] [nvarchar](150) NULL,
	[CommerceChamberNumber] [nvarchar](150) NULL,
	[PassportsAccountNumber] [nvarchar](150) NULL,
	[AgreementNumber] [nvarchar](150) NULL,
	[MailBoxOffice] [nvarchar](200) NULL,
	[MailBoxNumber] [nvarchar](200) NULL,
	[PhoneNumber] [nvarchar](200) NULL,
	[FullAddress] [nvarchar](200) NULL,
	[FullAddressArabic] [nvarchar](200) NULL,
	[MDT] [datetime] NULL,
 CONSTRAINT [PK_Foundations] PRIMARY KEY CLUSTERED 
(
	[FoundationCode] 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].[EmployeeLaborOffice] ADD  CONSTRAINT [DF_EmployeeLaborOffice_LaborOfficeExist]  DEFAULT ((0)) FOR [LaborOfficeExist]
GO
ALTER TABLE [dbo].[EmployeeLaborOffice] ADD  CONSTRAINT [DF_EmployeeLaborOffice_EstablishmentCode]  DEFAULT ((0)) FOR [EstablishmentCode]
GO
ALTER TABLE [dbo].[Establishments] ADD  CONSTRAINT [DF_Establishments_Closed]  DEFAULT ((0)) FOR [Closed]
GO

alter table Divisions
add GovernmentRelationsOfficer int null
GO
INSERT [dbo].[UserType] ([UserTypeCode], [Name], [Arabic_Name], [HaveEmployee], [OrganizationCategoryCode]) VALUES (28, N'Government Relations Officer', N'مسؤول العلاقات الحكومية', 1, NULL)
Go
-----------------------------End 2020-12-06-------------------------------



CREATE TABLE [dbo].[WF_FinancialPaymentHrRequest](
	[MasterRequestCode] [int] NOT NULL,
	[DivisionCode] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_WF_FinancialPaymentRequestHR] 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_FinancialPaymentHrRequest]  WITH CHECK ADD  CONSTRAINT [FK_WF_FinancialPaymentHrRequest_Divisions] FOREIGN KEY([DivisionCode])
REFERENCES [dbo].[Divisions] ([DivisionCode])
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequest] CHECK CONSTRAINT [FK_WF_FinancialPaymentHrRequest_Divisions]
GO

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

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequest] CHECK CONSTRAINT [FK_WF_FinancialPaymentHrRequest_WF_MasterRequests]
GO




CREATE TABLE [dbo].[WF_FinancialPaymentHrRequestDetails](
	[FinancialPaymentHrRequestDetailCode] [int] IDENTITY(1,1) NOT NULL,
	[MasterRequestCode] [int] NOT NULL,
	[FinancialPaymentTypeCode] [int] NOT NULL,
	[CostCenterCode] [int] NOT NULL,
	[InvoiceNumber] [nvarchar](50) NULL,
	[InvoiceAmount] [float] NULL,
	[InvoiceDate] [datetime] NULL,
	[Notes] [nvarchar](500) NULL,
 CONSTRAINT [PK_WF_FinancialPaymentHrRequestDetails] PRIMARY KEY CLUSTERED 
(
	[FinancialPaymentHrRequestDetailCode] 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_FinancialPaymentHrRequestDetails]  WITH CHECK ADD  CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_CostCenter] FOREIGN KEY([CostCenterCode])
REFERENCES [dbo].[WF_CostCenter] ([CostCenterCode])
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequestDetails] CHECK CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_CostCenter]
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequestDetails]  WITH CHECK ADD  CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_FinancialPaymentHrRequest] FOREIGN KEY([MasterRequestCode])
REFERENCES [dbo].[WF_FinancialPaymentHrRequest] ([MasterRequestCode])
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequestDetails] CHECK CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_FinancialPaymentHrRequest]
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequestDetails]  WITH CHECK ADD  CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_FinancialPaymentType] FOREIGN KEY([FinancialPaymentTypeCode])
REFERENCES [dbo].[WF_FinancialPaymentType] ([FinancialPaymentTypeCode])
GO

ALTER TABLE [dbo].[WF_FinancialPaymentHrRequestDetails] CHECK CONSTRAINT [FK_WF_FinancialPaymentHrRequestDetails_WF_FinancialPaymentType]
GO


SET IDENTITY_INSERT [dbo].[EmailTemplateType] ON 
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (170, N'Financial Payment Hr Request Sending', N'ارسال طلب السدادات المالية للموارد البشرية', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (171, N'Financial Payment Hr Request Accept', N'الموافقة طلب السدادات المالية للموارد البشرية', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (172, N'Financial Payment Hr Request Refuse', N'رفض طلب السدادات المالية  للموارد البشرية', 0, 1)
INSERT [dbo].[EmailTemplateType] ([EmailTemplateTypeCode], [Name], [Arabic_Name], [IsEditable], [AppModuleCode]) VALUES (173, N'Financial Payment Hr Request Cancel', N'الغاء طلب السدادات المالية للموارد البشرية', 0, 1)
SET IDENTITY_INSERT [dbo].[EmailTemplateType] OFF

SET IDENTITY_INSERT [dbo].[EmailTemplate] ON 
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (170, N'Financial Payment Hr Sending', N'ارسال طلب السدادات المالية للموارد البشرية', 170, N'ارسال طلب السدادات المالية', N' نرجو الموافقة على طلب السدادات المالية', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (171, N'Financial Payment  Hr Accept', N'الموافقة على طلب السدادات المالية للموارد البشرية', 171, N'الموافقة على طلب السدادات المالية', N'<p>تمت الموافقة</p>', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (172, N'Financial Payment Hr  Request Refuse', N'رفض طلب السدادات المالية للموارد البشرية', 172, N'رفض طلب السدادات المالية', N'تم رفض الطلب', 1, NULL)
INSERT [dbo].[EmailTemplate] ([EmailTemplateCode], [Name], [Arabic_Name], [EmailTemplateTypeCode], [Subject], [Body], [IsDefault], [SubjectAppendCode]) VALUES (173, N'Financial Payment Hr Request Cancellation', N'الغاء طلب السدادات المالية للموارد البشرية', 173, N'الغاء طلب السدادات المالية', N'تم إلغاء الطلب', 1, NULL)
SET IDENTITY_INSERT [dbo].[EmailTemplate] OFF


INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (399, N'Financial Payment Employee Hr Request', N'طلب السدادات المالية للموارد البشرية', 29, N'/WorkFlow/FinancialPaymentHrRequest', N'Index', N'fa fa-thumb-tack', N'get', 43, 1, 1)
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (400, N'Financial Payment Employee Hr Request Signature', N'إعتماد طلب السدادات المالية للموارد البشرية', 108, N'/WorkFlow/FinancialPaymentHrRequestSignature', N'Index', N'fa fa-thumb-tack', N'get', 46, 1, 1)
Go

INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3616, N'طلب السدادات المالية للموارد البشرية', N'Financial Payment Hr Request  ', 399, 1)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3617, N'عرض طلب السدادات المالية للموارد البشرية', N'View Financial Payment Hr Request', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3618, N'اضافة طلب السدادات المالية', N'Add Financial Payment Hr Request', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3619, N'تعديل طلب السدادات المالية للموارد البشرية', N'Edit Financial Payment Hr Request', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3620, N'حفظ طلب السدادات المالية للموارد البشرية', N'Save Financial Payment Hr Request', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3621, N'حذف طلب السدادات المالية للموارد البشرية', N'Delete Financial Payment Hr Request       ', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3622, N'الغاء طلب السدادات المالية للموارد البشرية', N'Cancel Financial Payment Hr Request ', 399, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3623, N'اعتماد طلب السدادات المالية للموارد البشرية', N' Financial Payment Hr Request Signature', 400, 1)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3624, N'عرض اعتماد طلب السدادات المالية للموارد البشرية', N'View  Financial Payment Hr Request Signature', 400, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3625, N'تعديل اعتماد طلب السدادات المالية للموارد البشرية', N'Edit  Financial Payment Hr Request Signature', 400, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3626, N'حفظ اعتماد طلب السدادات المالية للموارد البشرية', N'Save  Financial Payment Hr Request Signature', 400, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3627, N'حفظ متعدد اعتماد  السدادات المالية للموارد البشرية', N' Multiple Save  Financial Payment Signature', 400, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3628, N'ارجاع طلب السدادات المالية للموارد البشرية', N'Inverse  Financial Payment Hr Request', 400, 2)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (3629, N'اختيار كل الطلبات', N'Choose All Requests', 400, 2)
Go


INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (399, 1)
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (400, 1)
Go

INSERT [dbo].[WF_RequestType] ([RequestTypeId], [RequestTypeName], [RequestTypeNameEn], [IsEditable], [SenderUserName], [SenderFrom], [SenderPassword], [SenderHostName], [SenderPort], [SenderName], [SubmitBeforRequestDate], [SubmitAfterRequestDate], [IsUsed]) VALUES (31, N'طلب السدادات المالية للموارد البشرية', N'Financial Payment  Hr Request', 0, N'sayedmustafa84@yahoo.com', N'sayedmustafa84@yahoo.com', N'1', N'smtp.sendgrid.net', 587, N'SST', NULL, NULL, 1)
Go