-----------------------Hammad 2021-03-18------------------------------

CREATE TABLE [dbo].[CloseAttendance](
	[CloseAttendanceCode] [int] IDENTITY(1,1) NOT NULL,
	[Period] [tinyint] NOT NULL,
	[PYear] [smallint] NOT NULL,
	[SectionCode] [nvarchar](20) NOT NULL,
	[CreatedBy] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[ModifiedBy] [int] NULL,
	[ModificationDate] [datetime] NULL,
 CONSTRAINT [PK_CloseAttendance] PRIMARY KEY CLUSTERED 
(
	[CloseAttendanceCode] 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].[CloseAttendanceDetail](
	[CloseAttendanceDetailCode] [int] IDENTITY(1,1) NOT NULL,
	[CloseAttendanceCode] [int] NOT NULL,
	[EmployeeCode] [int] NOT NULL,
 CONSTRAINT [PK_CloseAttendanceDetail] PRIMARY KEY CLUSTERED 
(
	[CloseAttendanceDetailCode] 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].[CloseAttendanceDetail]  WITH CHECK ADD  CONSTRAINT [FK_CloseAttendanceDetail_CloseAttendance] FOREIGN KEY([CloseAttendanceCode])
REFERENCES [dbo].[CloseAttendance] ([CloseAttendanceCode])
GO

ALTER TABLE [dbo].[CloseAttendanceDetail] CHECK CONSTRAINT [FK_CloseAttendanceDetail_CloseAttendance]
GO

-----------------------End 2021-03-18------------------------------
-----------------------Mahmoud ELsayed 2021-03-21------------------
create view SystemMenuReportView            
as
SELECT          SystemMenu_1.SystemMenuCode AS SystemMenuCode1, SystemMenu_1.Name AS Name1, SystemMenu_1.Arabic_Name AS Arabic_Name1, SystemMenu_1.ParentID AS ParentID1, 
                         SystemMenu_3.Sequence AS Sequence1, SystemMenu_2.SystemMenuCode AS SystemMenuCode2, SystemMenu_2.Name AS Name2, SystemMenu_2.Arabic_Name AS Arabic_Name2, SystemMenu_2.ParentID AS ParentID2, 
                         SystemMenu_4.Sequence AS Sequence2, SystemMenu_3.SystemMenuCode AS SystemMenuCode3, SystemMenu_3.Name AS Name3, SystemMenu_3.Arabic_Name AS Arabic_Name3, SystemMenu_3.ParentID AS ParentID3, 
                         SystemMenu_5.Sequence AS Sequence3, SystemMenu_4.SystemMenuCode AS SystemMenuCode4, SystemMenu_4.Name AS Name4, SystemMenu_4.Arabic_Name AS Arabic_Name4, SystemMenu_4.ParentID AS ParentID4, 
                         SystemMenu_2.Sequence AS Sequence4, SystemMenu_5.SystemMenuCode AS SystemMenuCode5, SystemMenu_5.Name AS Name5, SystemMenu_5.Arabic_Name AS StoreTransactionMaster5, 
                         SystemMenu_5.ParentID AS ParentID5, SystemMenu_1.Sequence AS Sequence5, iif(SystemMenu_5.SystemMenuCode IS NOT NULL, SystemMenu_5.SystemMenuCode, iif(SystemMenu_4.SystemMenuCode IS NOT NULL, 
                         SystemMenu_4.SystemMenuCode, iif(SystemMenu_3.SystemMenuCode IS NOT NULL, SystemMenu_3.SystemMenuCode, iif(SystemMenu_2.SystemMenuCode IS NOT NULL, SystemMenu_2.SystemMenuCode, 
                         SystemMenu_1.SystemMenuCode)))) AS SystemMenuCodeMain
FROM            dbo.SystemMenu AS SystemMenu_3 LEFT OUTER JOIN
                         dbo.SystemMenu AS SystemMenu_4 ON SystemMenu_3.SystemMenuCode = SystemMenu_4.ParentID LEFT OUTER JOIN
                         dbo.SystemMenu AS SystemMenu_5 ON SystemMenu_4.SystemMenuCode = SystemMenu_5.ParentID RIGHT OUTER JOIN
                         dbo.SystemMenu AS SystemMenu_2 ON SystemMenu_3.ParentID = SystemMenu_2.SystemMenuCode RIGHT OUTER JOIN
                         dbo.SystemMenu AS SystemMenu_1 ON SystemMenu_2.ParentID = SystemMenu_1.SystemMenuCode
WHERE        (SystemMenu_1.ParentID IS NULL)
-----------------------End 2021-03-21------------------------------------------