﻿
-------------------------------Mostafa 2022-03-27---
CREATE TABLE [Attendance].[Machine](
	[MachineCode] [int] IDENTITY(1,1) NOT NULL,
	[IPAddress] [nvarchar](150) NULL,
	[MachineNo] [nvarchar](150) NULL,
	[Port] [int] NOT NULL,
	[In] [nvarchar](max) NULL,
	[Out] [nvarchar](max) NULL,
	[HavePassword] [bit] NOT NULL,
	[Password] [nvarchar](max) NULL,
	[Audit_CreatedBy] [int] NULL,
	[Audit_CreationDate] [datetime] NULL,
	[Audit_ModifiedBy] [int] NULL,
	[Audit_ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_Machine] PRIMARY KEY CLUSTERED 
(
	[MachineCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [Attendance].[Machine] ADD  DEFAULT (CONVERT([bit],(0))) FOR [HavePassword]
GO

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(600,N'Machine',N'ماكينة',350,N'/Attendance/Machine',N'index','fa fa-hand-o-up',N'get',3,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(600,11)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13086,N'ماكينة',N'Machine',600,1)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13087,N'عرض ماكينة',N'View Machine',600,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13088,N'إضافة ماكينة',N'Add Machine',600,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13089,N'تعديل ماكينة',N'Edit Machine',600,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13090,N'حذف ماكينة',N'Delete Machine',600,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13091,N'حفظ ماكينة',N'Save Machine',600,2)
Go

-- SSTAuthorizationActionFilterBefore (script only not added in db Check it and run if you want) 

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1002,N'Attendance',N'Machine',N'Index',0,N'get',13086)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1003,N'Attendance',N'Machine',N'Machine_Read',0,N'post',13087)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1004,N'Attendance',N'Machine',N'Save',1,N'get',13088)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1005,N'Attendance',N'Machine',N'Save',2,N'get',13089)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1006,N'Attendance',N'Machine',N'Delete',0,N'post',13090)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1007,N'Attendance',N'Machine',N'Save',0,N'post',13091)

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
VALUES(601,N'Machine Transaction',N'حركات الماكينة',350,N'/Attendance/MachineTransaction',N'Index','fa fa-cog',N'get',4,1,1)
Go

INSERT[dbo].[SystemMenuModule]( [SystemMenuCode], [AppModuleCode]) VALUES(601,11)
Go

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13092,N'حركات الماكينة',N'Machine Transaction',601,1)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13093,N'عرض حركات الماكينة',N'View Machine Transaction',601,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13094,N'إضافة حركات الماكينة',N'Add Machine Transaction',601,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13095,N'تعديل حركات الماكينة',N'Edit Machine Transaction',601,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13096,N'حذف حركات الماكينة',N'Delete Machine Transaction',601,2)

INSERT[dbo].[SecurityPrivilege]([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES(13097,N'حفظ حركات الماكينة',N'Save Machine Transaction',601,2)
Go


INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1008,N'Attendance',N'MachineTransaction',N'Index',0,N'get',13092)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1009,N'Attendance',N'MachineTransaction',N'MachineTransaction_Read',0,N'post',13093)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1010,N'Attendance',N'MachineTransaction',N'Save',1,N'get',13094)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1011,N'Attendance',N'MachineTransaction',N'Save',2,N'get',13095)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1012,N'Attendance',N'MachineTransaction',N'Delete',0,N'post',13096)

INSERT [dbo].[SecurityPrivilegeActions] ([ID], [AreaName], [ControllerName], [ActionName], [ActionType], [HTTPMethod], [SecurityPrivilegeID]) VALUES(1013,N'Attendance',N'MachineTransaction',N'Save',0,N'post',13097)
Go
-------------------------------End 2022-03-27---
-------------------------------Ali 2022-03-28---

 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId])
VALUES(N'ViewReportInCrystal',N'View Report In Crystal',N'عرض التقرير فى الكريستال',N'false',10,19,1,1,Null,Null,null)
Go
-------------------------------End 2022-03-28---

-------------------------------Ali 2022-03-29---

 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId])
VALUES(N'WFMaximumRequestNumberPerDay',N'Maximum Request Number Per Day',N'الحد الاقصى لعدد الطلبات فى اليوم',N'1',2,1,2,1,Null,Null,19)
Go
 INSERT[dbo].[ConfigParameter]  ([ConfigKey], [Name], [Arabic_Name], [ConfigValue], [ConfigCategoryCode], [OrderNo], [ConfigType], [IsVisible], [ModifiedBy], [ModificationDate], [RequestTypeId])
VALUES(N'WFRequestNumberPerMonth',N'Request Number Per Month',N'عدد الطلبات فى الشهر',N'30',2,2,2,1,Null,Null,19)
Go
-------------------------------End 2022-03-29---
-------------------------------Hammad 2022-03-29---

ALTER TABLE dbo.Reports ADD
	AllClient bit NOT NULL CONSTRAINT DF_Reports_AllClient_1 DEFAULT ((0)),
	NeedPublish bit NOT NULL CONSTRAINT DF_Reports_NeedPublish_1 DEFAULT ((0)),
	AvailableToCustomer bit NOT NULL CONSTRAINT DF_Reports_AvailableToCustomer_1 DEFAULT ((0)),
	LanguageId tinyint NOT NULL CONSTRAINT DF_Reports_LanguageId_1 DEFAULT ((1)),
	FixedFormula nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	Description nvarchar(500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	PassFixedFormulaToSupReport bit NOT NULL CONSTRAINT DF_Reports_PassFixedFormulaToSupReport_1 DEFAULT ((0)),
	ShowGroup bit NOT NULL CONSTRAINT DF_Reports_ShowGroup_1 DEFAULT ((0)),
	ShowSeparatedPage bit NOT NULL CONSTRAINT DF_Reports_ShowSeparatedPage_1 DEFAULT ((0)),
	DefaultTableGroupPrefix int NULL,
	DefaultGroupCode int NULL,
	Sortable bit NOT NULL CONSTRAINT DF_Reports_Sortable_1 DEFAULT ((0))
GO


CREATE TABLE [dbo].[ReportsAppModules](
	[ReportsAppModuleCode] [int] IDENTITY(1,1) NOT NULL,
	[ReportCode] [int] NOT NULL,
	[AppModuleCode] [int] NOT NULL,
 CONSTRAINT [PK_ReportsAppModules] PRIMARY KEY CLUSTERED 
(
	[ReportsAppModuleCode] 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].[ReportsAppModules]  WITH CHECK ADD  CONSTRAINT [FK_ReportsAppModules_Reports] FOREIGN KEY([ReportCode])
REFERENCES [dbo].[Reports] ([ReportCode])
GO

ALTER TABLE [dbo].[ReportsAppModules] CHECK CONSTRAINT [FK_ReportsAppModules_Reports]
GO


CREATE TABLE [dbo].[ReportCountry](
	[ReportCountryCode] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[Arabic_Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ReportCountry] PRIMARY KEY CLUSTERED 
(
	[ReportCountryCode] 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].[ReportsClient](
	[ReportsClientCode] [int] IDENTITY(1,1) NOT NULL,
	[ReportCode] [int] NOT NULL,
	[ClientCode] [int] NOT NULL,
 CONSTRAINT [PK_ReportsClient] PRIMARY KEY CLUSTERED 
(
	[ReportsClientCode] 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].[ReportsClient]  WITH CHECK ADD  CONSTRAINT [FK_ReportsClient_Reports] FOREIGN KEY([ReportCode])
REFERENCES [dbo].[Reports] ([ReportCode])
GO

ALTER TABLE [dbo].[ReportsClient] CHECK CONSTRAINT [FK_ReportsClient_Reports]
GO

CREATE TABLE [dbo].[ReportsExceptCountries](
	[ReportsExceptCountryCode] [int] IDENTITY(1,1) NOT NULL,
	[ReportCode] [int] NOT NULL,
	[ReportCountryCode] [int] NOT NULL,
 CONSTRAINT [PK_ReportsExceptCountries] PRIMARY KEY CLUSTERED 
(
	[ReportsExceptCountryCode] 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].[ReportsExceptCountries]  WITH CHECK ADD  CONSTRAINT [FK_ReportsExceptCountries_ReportCountry] FOREIGN KEY([ReportCountryCode])
REFERENCES [dbo].[ReportCountry] ([ReportCountryCode])
GO

ALTER TABLE [dbo].[ReportsExceptCountries] CHECK CONSTRAINT [FK_ReportsExceptCountries_ReportCountry]
GO

ALTER TABLE [dbo].[ReportsExceptCountries]  WITH CHECK ADD  CONSTRAINT [FK_ReportsExceptCountries_Reports] FOREIGN KEY([ReportCode])
REFERENCES [dbo].[Reports] ([ReportCode])
GO

ALTER TABLE [dbo].[ReportsExceptCountries] CHECK CONSTRAINT [FK_ReportsExceptCountries_Reports]
GO


CREATE TABLE [dbo].[Parameters](
	[ParameterCode] [int] NOT NULL,
	[DescriptionAr] [nvarchar](50) NOT NULL,
	[DescriptionEn] [nvarchar](50) NOT NULL,
	[ParameterName] [varchar](50) NOT NULL,
	[LookupTableId] [int] NULL,
	[DataType] [int] NULL,
 CONSTRAINT [PK_Parameters] PRIMARY KEY CLUSTERED 
(
	[ParameterCode] 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].[ParameterReports](
	[ParameterReportCode] [int] IDENTITY(1,1) NOT NULL,
	[ParameterCode] [int] NOT NULL,
	[ReportCode] [int] NOT NULL,
	[IsRequired] [bit] NOT NULL,
	[DefaultValueFrom] [nvarchar](100) NULL,
	[DefaultValueTo] [nvarchar](100) NULL,
	[SelectionTable] [int] NULL,
	[DisplayMultiSelect] [bit] NOT NULL,
 CONSTRAINT [PK_ParameterReports] PRIMARY KEY CLUSTERED 
(
	[ParameterReportCode] 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].[ParameterReports] ADD  CONSTRAINT [DF_ParameterReports_IsRequired]  DEFAULT ((0)) FOR [IsRequired]
GO

ALTER TABLE [dbo].[ParameterReports] ADD  CONSTRAINT [DF_ParameterReports_DisplayMultiSelect]  DEFAULT ((0)) FOR [DisplayMultiSelect]
GO

ALTER TABLE [dbo].[ParameterReports]  WITH CHECK ADD  CONSTRAINT [FK_ParameterReports_Parameters] FOREIGN KEY([ParameterCode])
REFERENCES [dbo].[Parameters] ([ParameterCode])
GO

ALTER TABLE [dbo].[ParameterReports] CHECK CONSTRAINT [FK_ParameterReports_Parameters]
GO

ALTER TABLE [dbo].[ParameterReports]  WITH CHECK ADD  CONSTRAINT [FK_ParameterReports_Reports] FOREIGN KEY([ReportCode])
REFERENCES [dbo].[Reports] ([ReportCode])
GO

ALTER TABLE [dbo].[ParameterReports] CHECK CONSTRAINT [FK_ParameterReports_Reports]
GO



CREATE TABLE [dbo].[LookupTables](
	[LookupTableId] [int] IDENTITY(1,1) NOT NULL,
	[TableName] [nvarchar](50) NOT NULL,
	[ControllerName] [nvarchar](100) NOT NULL,
	[ActionName] [nvarchar](100) NOT NULL,
	[DataValueField] [nvarchar](50) NOT NULL,
	[DataTextFieldEn] [nvarchar](50) NOT NULL,
	[DataTextFieldAr] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_LookupTables] PRIMARY KEY CLUSTERED 
(
	[LookupTableId] 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].[LookupTableGroup](
	[LookupTableGroupCode] [int] IDENTITY(1,1) NOT NULL,
	[ColumnName] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_LookupTableGroup] PRIMARY KEY CLUSTERED 
(
	[LookupTableGroupCode] 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].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
 VALUES (598, N'Reports setting', N'اعداد التقارير', 208, N'/ReportsSetting', N'Save', N'fa fa-thumb-tack', N'get', 7, 0, 0)
GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
 VALUES (602, N' Reports', N'التقارير ', 27, NULL, NULL, N'fa fa-thumb-tack', NULL, 5, 1, 0)
GO
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage])
 VALUES (603, N' Report Search', N'  التقارير المجمعة ', 602, N'/Personal/ReportSearch', N'Index', N'fa fa-thumb-tack', N'get', 1, 1, 0)
GO
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (602, 6)
GO
INSERT [dbo].[SystemMenuModule] ([SystemMenuCode], [AppModuleCode]) VALUES (603, 6)
GO

SET IDENTITY_INSERT [dbo].[ReportCountry] ON 
GO
INSERT [dbo].[ReportCountry] ([ReportCountryCode], [Name], [Arabic_Name]) VALUES (1, N'Egypt', N'مصر')
GO
INSERT [dbo].[ReportCountry] ([ReportCountryCode], [Name], [Arabic_Name]) VALUES (2, N'Saudi Arabia', N'السعودية')
GO
SET IDENTITY_INSERT [dbo].[ReportCountry] OFF
GO

insert into ReportsAppModules (ReportCode,AppModuleCode) (select ReportCode,AppModuleCode from Reports)
Go

ALTER TABLE dbo.Reports
	DROP COLUMN AppModuleCode
GO

-------------------------------End 2022-03-29---