 -------------------------------Ali 2020-10-05---------------------

 ALTER TABLE dbo.AppModule ADD
	LicenseNumber nvarchar(1000) NULL
GO



CREATE PROCEDURE [dbo].[Emp_GetEmployeeBalanceCheck] 
(
	@EmpCode	int,
	@PYear	int
)
AS
SELECT 

       [EmployeeCode]
	   ,[PYear]
	   ,[Open_Balanc]
	  ,[Annual01]
      ,[Annual02]
      ,[Annual03]
      ,[Annual04]
      ,[Annual05]
      ,[Annual06]
      ,[Annual07]
      ,[Annual08]
      ,[Annual09]
      ,[Annual10]
      ,[Annual11]
      ,[Annual12]

	  ,[Annual01]+[Annual02]+[Annual03]+[Annual04]+[Annual05]+[Annual06]+[Annual07]+
	  [Annual08]+[Annual09]+[Annual10]+[Annual11]+[Annual12] as Total_Annual

	  ,[DiscountAnnual01]
      ,[DiscountAnnual02]
      ,[DiscountAnnual03]
      ,[DiscountAnnual04]
      ,[DiscountAnnual05]
      ,[DiscountAnnual06]
      ,[DiscountAnnual07]
      ,[DiscountAnnual08]
      ,[DiscountAnnual09]
      ,[DiscountAnnual10]
      ,[DiscountAnnual11]
      ,[DiscountAnnual12]

	  ,[DiscountAnnual01]+[DiscountAnnual02]+[DiscountAnnual03]+[DiscountAnnual04]+
	  [DiscountAnnual05]+[DiscountAnnual06]+[DiscountAnnual07]+[DiscountAnnual08]+
	  [DiscountAnnual09]+[DiscountAnnual10]+[DiscountAnnual11]+[DiscountAnnual12] as Total_DiscountAnnual

      ,[ConsumeAnnual01]
      ,[ConsumeAnnual02]
      ,[ConsumeAnnual03]
      ,[ConsumeAnnual04]
      ,[ConsumeAnnual05]
      ,[ConsumeAnnual06]
      ,[ConsumeAnnual07]
      ,[ConsumeAnnual08]
      ,[ConsumeAnnual09]
      ,[ConsumeAnnual10]
      ,[ConsumeAnnual11]
      ,[ConsumeAnnual12]

	  ,[ConsumeAnnual01]+[ConsumeAnnual02]+[ConsumeAnnual03]+[ConsumeAnnual04]+
	  [ConsumeAnnual05]+[ConsumeAnnual06]+[ConsumeAnnual07]+[ConsumeAnnual08]+
	  [ConsumeAnnual09]+[ConsumeAnnual10]+[ConsumeAnnual11]+[ConsumeAnnual12] as Total_ConsumeAnnual

      ,[Mytotal]
      ,[Dayoff01]
      ,[Dayoff02]
      ,[Dayoff03]
      ,[Dayoff04]
      ,[Dayoff05]
      ,[Dayoff06]
      ,[Dayoff07]
      ,[Dayoff08]
      ,[Dayoff09]
      ,[Dayoff10]
      ,[Dayoff11]
      ,[Dayoff12]

	  ,[Dayoff01]+[Dayoff02]+[Dayoff03]+[Dayoff04]+[Dayoff05]+[Dayoff06]+[Dayoff07]+
	  [Dayoff08]+[Dayoff09]+[Dayoff10]+[Dayoff11]+[Dayoff12] as Total_Dayoff

      ,[DiscountDayoff01]
      ,[DiscountDayoff02]
      ,[DiscountDayoff03]
      ,[DiscountDayoff04]
      ,[DiscountDayoff05]
      ,[DiscountDayoff06]
      ,[DiscountDayoff07]
      ,[DiscountDayoff08]
      ,[DiscountDayoff09]
      ,[DiscountDayoff10]
      ,[DiscountDayoff11]
      ,[DiscountDayoff12]

	  ,[DiscountDayoff01]+[DiscountDayoff02]+[DiscountDayoff03]+[DiscountDayoff04]+[DiscountDayoff05]+[DiscountDayoff06]+[DiscountDayoff07]+
	  [DiscountDayoff08]+[DiscountDayoff09]+[DiscountDayoff10]+[DiscountDayoff11]+[DiscountDayoff12] as Total_DiscountDayoff

      ,[ConsumeDayoff01]
      ,[ConsumeDayoff02]
      ,[ConsumeDayoff03]
      ,[ConsumeDayoff04]
      ,[ConsumeDayoff05]
      ,[ConsumeDayoff06]
      ,[ConsumeDayoff07]
      ,[ConsumeDayoff08]
      ,[ConsumeDayoff09]
      ,[ConsumeDayoff10]
      ,[ConsumeDayoff11]
      ,[ConsumeDayoff12]

	  ,[ConsumeDayoff01]+[ConsumeDayoff02]+[ConsumeDayoff03]+[ConsumeDayoff04]+[ConsumeDayoff05]+[ConsumeDayoff06]+[ConsumeDayoff07]+
	  [ConsumeDayoff08]+[ConsumeDayoff09]+[ConsumeDayoff10]+[ConsumeDayoff11]+[ConsumeDayoff12] as Total_ConsumeDayoff

      ,[Holiday01]
      ,[Holiday02]
      ,[Holiday03]
      ,[Holiday04]
      ,[Holiday05]
      ,[Holiday06]
      ,[Holiday07]
      ,[Holiday08]
      ,[Holiday09]
      ,[Holiday10]
      ,[Holiday11]
      ,[Holiday12]

	   ,[Holiday01]+[Holiday02]+[Holiday03]+[Holiday04]+[Holiday05]+[Holiday06]+[Holiday07]+
	  [Holiday08]+[Holiday09]+[Holiday10]+[Holiday11]+[Holiday12] as Total_Holiday


      ,[DiscountHoliday01]
      ,[DiscountHoliday02]
      ,[DiscountHoliday03]
      ,[DiscountHoliday04]
      ,[DiscountHoliday05]
      ,[DiscountHoliday06]
      ,[DiscountHoliday07]
      ,[DiscountHoliday08]
      ,[DiscountHoliday09]
      ,[DiscountHoliday10]
      ,[DiscountHoliday11]
      ,[DiscountHoliday12]

	 ,[DiscountHoliday01]+[DiscountHoliday02]+[DiscountHoliday03]+[DiscountHoliday04]+[DiscountHoliday05]+[DiscountHoliday06]+[DiscountHoliday07]+
	  [DiscountHoliday08]+[DiscountHoliday09]+[DiscountHoliday10]+[DiscountHoliday11]+[DiscountHoliday12] as Total_DiscountHoliday

      ,[ConsumeHoliday01]
      ,[ConsumeHoliday02]
      ,[ConsumeHoliday03]
      ,[ConsumeHoliday04]
      ,[ConsumeHoliday05]
      ,[ConsumeHoliday06]
      ,[ConsumeHoliday07]
      ,[ConsumeHoliday08]
      ,[ConsumeHoliday09]
      ,[ConsumeHoliday10]
      ,[ConsumeHoliday11]
      ,[ConsumeHoliday12]

	   ,[ConsumeHoliday01]+[ConsumeHoliday02]+[ConsumeHoliday03]+[ConsumeHoliday04]+[ConsumeHoliday05]+[ConsumeHoliday06]+[ConsumeHoliday07]+
	  [ConsumeHoliday08]+[ConsumeHoliday09]+[ConsumeHoliday10]+[ConsumeHoliday11]+[ConsumeHoliday12] as Total_ConsumeHoliday


	  --//Due Total 
	  ,[Annual01]+[Dayoff01]+[Holiday01] as Due_Total_1
	  ,[Annual02]+[Dayoff02]+[Holiday02] as Due_Total_2
	  ,[Annual03]+[Dayoff03]+[Holiday03] as Due_Total_3
	  ,[Annual04]+[Dayoff04]+[Holiday04] as Due_Total_4
	  ,[Annual05]+[Dayoff05]+[Holiday05] as Due_Total_5
	  ,[Annual06]+[Dayoff06]+[Holiday06] as Due_Total_6
	  ,[Annual07]+[Dayoff07]+[Holiday07] as Due_Total_7
	  ,[Annual08]+[Dayoff08]+[Holiday08] as Due_Total_8
	  ,[Annual09]+[Dayoff09]+[Holiday09] as Due_Total_9
	  ,[Annual10]+[Dayoff10]+[Holiday10] as Due_Total_10
	  ,[Annual11]+[Dayoff11]+[Holiday11] as Due_Total_11
	  ,[Annual12]+[Dayoff12]+[Holiday12] as Due_Total_12

	  --//Consume Total 
	  ,[ConsumeAnnual01]+[ConsumeDayoff01]+[ConsumeHoliday01] as Consume_Total_1
	  ,[ConsumeAnnual02]+[ConsumeDayoff02]+[ConsumeHoliday02] as Consume_Total_2
	  ,[ConsumeAnnual03]+[ConsumeDayoff03]+[ConsumeHoliday03] as Consume_Total_3
	  ,[ConsumeAnnual04]+[ConsumeDayoff04]+[ConsumeHoliday04] as Consume_Total_4
	  ,[ConsumeAnnual05]+[ConsumeDayoff05]+[ConsumeHoliday05] as Consume_Total_5
	  ,[ConsumeAnnual06]+[ConsumeDayoff06]+[ConsumeHoliday06] as Consume_Total_6
	  ,[ConsumeAnnual07]+[ConsumeDayoff07]+[ConsumeHoliday07] as Consume_Total_7
	  ,[ConsumeAnnual08]+[ConsumeDayoff08]+[ConsumeHoliday08] as Consume_Total_8
	  ,[ConsumeAnnual09]+[ConsumeDayoff09]+[ConsumeHoliday09] as Consume_Total_9
	  ,[ConsumeAnnual10]+[ConsumeDayoff10]+[ConsumeHoliday10] as Consume_Total_10
	  ,[ConsumeAnnual11]+[ConsumeDayoff11]+[ConsumeHoliday11] as Consume_Total_11
	  ,[ConsumeAnnual12]+[ConsumeDayoff12]+[ConsumeHoliday12] as Consume_Total_12


	   --//Discount Total 
	  ,[DiscountAnnual01]+[DiscountDayoff01]+[DiscountHoliday01] as Discount_Total_1
	  ,[DiscountAnnual02]+[DiscountDayoff02]+[DiscountHoliday02] as Discount_Total_2
	  ,[DiscountAnnual03]+[DiscountDayoff03]+[DiscountHoliday03] as Discount_Total_3
	  ,[DiscountAnnual04]+[DiscountDayoff04]+[DiscountHoliday04] as Discount_Total_4
	  ,[DiscountAnnual05]+[DiscountDayoff05]+[DiscountHoliday05] as Discount_Total_5
	  ,[DiscountAnnual06]+[DiscountDayoff06]+[DiscountHoliday06] as Discount_Total_6
	  ,[DiscountAnnual07]+[DiscountDayoff07]+[DiscountHoliday07] as Discount_Total_7
	  ,[DiscountAnnual08]+[DiscountDayoff08]+[DiscountHoliday08] as Discount_Total_8
	  ,[DiscountAnnual09]+[DiscountDayoff09]+[DiscountHoliday09] as Discount_Total_9
	  ,[DiscountAnnual10]+[DiscountDayoff10]+[DiscountHoliday10] as Discount_Total_10
	  ,[DiscountAnnual11]+[DiscountDayoff11]+[DiscountHoliday11] as Discount_Total_11
	  ,[DiscountAnnual12]+[DiscountDayoff12]+[DiscountHoliday12] as Discount_Total_12





	  ,[DiscountAnnual12]+[DiscountDayoff12]+[DiscountHoliday12] as Discount_Total_Total




	  ,[EndAnnualBalance]
      ,[EndDayoffBalance]
      ,[EndHolidayBalance]
      ,[EndTotalBalance]
      ,[AnnualBalance]
      ,[DayoffBalance]
      ,[HolidayBalance]

      ,[AnnualBalance01]
      ,[AnnualBalance02]
      ,[AnnualBalance03]
      ,[AnnualBalance04]
      ,[AnnualBalance05]
      ,[AnnualBalance06]
      ,[AnnualBalance07]
      ,[AnnualBalance08]
      ,[AnnualBalance09]
      ,[AnnualBalance10]
      ,[AnnualBalance11]
      ,[AnnualBalance12]

      ,[DayoffBalance01]
      ,[DayoffBalance02]
      ,[DayoffBalance03]
      ,[DayoffBalance04]
      ,[DayoffBalance05]
      ,[DayoffBalance06]
      ,[DayoffBalance07]
      ,[DayoffBalance08]
      ,[DayoffBalance09]
      ,[DayoffBalance10]
      ,[DayoffBalance11]
      ,[DayoffBalance12]

      ,[HolidayBalance01]
      ,[HolidayBalance02]
      ,[HolidayBalance03]
      ,[HolidayBalance04]
      ,[HolidayBalance05]
      ,[HolidayBalance06]
      ,[HolidayBalance07]
      ,[HolidayBalance08]
      ,[HolidayBalance09]
      ,[HolidayBalance10]
      ,[HolidayBalance11]
      ,[HolidayBalance12]

      ,[PYB2]
      ,[PYB3]


	  ,[AnnualBalance01]+[DayoffBalance01]+[HolidayBalance01] as Total_Balance_1
	  ,[AnnualBalance02]+[DayoffBalance01]+[HolidayBalance02] as Total_Balance_2
	  ,[AnnualBalance03]+[DayoffBalance03]+[HolidayBalance03] as Total_Balance_3
	  ,[AnnualBalance04]+[DayoffBalance04]+[HolidayBalance04] as Total_Balance_4
	  ,[AnnualBalance05]+[DayoffBalance05]+[HolidayBalance05] as Total_Balance_5
	  ,[AnnualBalance06]+[DayoffBalance06]+[HolidayBalance06] as Total_Balance_6
	  ,[AnnualBalance07]+[DayoffBalance07]+[HolidayBalance07] as Total_Balance_7
	  ,[AnnualBalance08]+[DayoffBalance08]+[HolidayBalance08] as Total_Balance_8
	  ,[AnnualBalance09]+[DayoffBalance09]+[HolidayBalance09] as Total_Balance_9
	  ,[AnnualBalance10]+[DayoffBalance10]+[HolidayBalance10] as Total_Balance_10
	  ,[AnnualBalance11]+[DayoffBalance11]+[HolidayBalance11] as Total_Balance_11
	  ,[AnnualBalance12]+[DayoffBalance12]+[HolidayBalance12] as Total_Balance_12


	  FROM [dbo].[VacationDue]
where [dbo].[VacationDue].[EmployeeCode] = @EmpCode and [dbo].[VacationDue].[PYear] =@PYear
 -------------------------------End 2020-10-05---------------------
