﻿-------------------------------------Sayed 2019-10-16--------------------------

CREATE TABLE [dbo].[WorkCube](
	[EmployeeCode] [int] NULL,
	[CubeIssueDate] [datetime] NULL,
	[CubeIssuePlace] [nvarchar](150) NULL,
	[CubeSavedDate] [datetime] NULL,
	[CubeIssueNumber] [nvarchar](100) NULL,
	[CubeIssueDateHijri] [nvarchar](10) NULL,
	[CubeSavedDateHijri] [nvarchar](10) NULL,
	[MDT] [datetime] NULL
) ON [PRIMARY]
GO
--------------------------------------End 2019-10-16---------------------------
--------------------------------------Hammad 2019-10-16---------------------------

Alter VIEW [Budget].[PreDashboardBudget]
AS
WITH BEDDates AS 
(
  SELECT BIC.BudgetItemCategoryCode,BIC.Arabic_Name as CategoryNameAR,BIC.Name as CategoryNameEN,BED.BudgetItemCode, BED.EmployeeCode, BED.NewAmount, BED.EndEffectYear, BED.EndEffectMonth,  BH.FromMonth,BH.FromYear,BH.[ToMonth],BH.[ToYear],BED.BudgetEmployeeDetailCode
  FROM [Budget].[BudgetEmployeeDetails] BED
  INNER JOIN [Budget].[BudgetItems] BI ON BED.BudgetItemCode=BI.BudgetItemCode
  INNER JOIN [Budget].[BudgetItemCategory] BIC ON BI.BudgetItemCategoryCode=BIC.BudgetItemCategoryCode  
  INNER JOIN [Budget].[BudgetHeads] BH ON BI.BudgetHeadCode=BH.BudgetHeadCode  
  WHERE (datefromparts(BED.EndEffectYear, BED.EndEffectMonth, 1) BETWEEN datefromparts(BH.FromYear, BH.FromMonth, 1) AND datefromparts(BH.[ToYear], BH.[ToMonth], 1))
)
, BED AS 
(
  SELECT BIC.BudgetItemCategoryCode,BIC.Arabic_Name as CategoryNameAR,BIC.Name as CategoryNameEN,  BI.BudgetHeadCode, BI.BudgetItemCode, BI.Name, BI.Arabic_Name, BudEmpDtls.EmployeeCode, BudEmpDtls.NewAmount, BI.SaveStatus  FROM [Budget].[BudgetEmployeeDetails] BudEmpDtls
  INNER JOIN [Budget].[BudgetItems] BI ON BudEmpDtls.BudgetItemCode=BI.BudgetItemCode
    INNER JOIN [Budget].[BudgetItemCategory] BIC ON BI.BudgetItemCategoryCode=BIC.BudgetItemCategoryCode  
  INNER JOIN [Budget].[BudgetHeads] BH ON BI.BudgetHeadCode=BH.BudgetHeadCode
)
SELECT BED.BudgetItemCategoryCode,BED.CategoryNameAR,BED.CategoryNameEN,BED.BudgetHeadCode,BED.BudgetItemCode, BED.Arabic_Name, BED.Name, BEDDates.BudgetEmployeeDetailCode,  b.Year, b.Month,BEDDates.EmployeeCode,BED.NewAmount, BED.SaveStatus FROM BED
INNER JOIN BEDDates ON BED.EmployeeCode=BEDDates.EmployeeCode AND BED.BudgetItemCode=BEDDates.BudgetItemCode
CROSS APPLY dbo.MonthsToRows(BEDDates.FromMonth,BEDDates.FromYear,BEDDates.EndEffectMonth,BEDDates.EndEffectYear) AS b

GO

INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (232, N'Budget Compare By Month Report', N'تقرير مقارنة الموازنة بالشهر   ', 231, N'/Budget/CompareByMonthReport', N'Index', NULL, N'get', 1, 1, 1)
INSERT [dbo].[SystemMenu] ([SystemMenuCode], [Name], [Arabic_Name], [ParentID], [ControllerName], [ActionName], [ImageURL], [HTTPMethod], [Sequence], [IsVisible], [ShowInPrivilegePage]) VALUES (231, N'Reports', N'التقارير', 203, NULL, NULL, N'fa fa-thumb-tack', NULL, 5, 1, 1)
Go
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (12050, N'التقارير', N'Reports', 231, 1)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (12051, N'تقرير مقارنة الموازنة بالشهر     ', N'Budget Compare By Month Report   ', 232, 1)
INSERT [dbo].[SecurityPrivilege] ([ID], [DescriptionAR], [DescriptionEN], [SystemMenuCode], [PrivilegeType]) VALUES (12052, N'عرض تقرير مقارنة الموازنة بالشهر     ', N'View Budget Compare By Month Report', 232, 2)
Go

INSERT [dbo].[SystemMenuModule] ( [SystemMenuCode], [AppModuleCode]) VALUES ( 231, 6)
INSERT [dbo].[SystemMenuModule] ( [SystemMenuCode], [AppModuleCode]) VALUES ( 232, 6)
Go

--------------------------------------End 2019-10-16---------------------------