20-03-2020-------------------DataBase Changes 2nd

DataBase Changes 




alter  proc [dbo].[ManageAttendance]
@Id int,
@User_Id int=null,  
@Coming_Time datetime=null,
@Going_Time datetime=null,
@ByDate datetime=null,
@StartDate datetime=null,
@EndDate  datetime=null,
@Status nvarchar(50)=null,
@Coming_Message nvarchar(max)=null,
@Going_Message nvarchar(max)=null,
@isMeetingGoodMorning int=0,
@Hr_Permission int=0,
@Actiontype char(10)
as
begin
 if(@Actiontype='Coming')
 begin
     insert into tbl_Attendance(User_Id,Coming_Time,Going_Time,Coming_Message,Status,isMeetingGoodMorning)
   values (@User_Id,@Coming_Time,@Going_Time,@Coming_Message,@Status,@isMeetingGoodMorning)
 end  
 else if(@Actiontype='select1')
 begin
     select a.*,datediff(month,b.date,getDate()) as numberOfMonth from tbl_Attendance a inner join tbl_Login b on a.User_Id=b.srno
  where a.User_Id=@User_Id
 end
 else if(@Actiontype='Going')
    begin
     update tbl_Attendance set
  Going_Time=@Going_Time,
  Status=@Status,
  Going_Message=@Going_Message,
  Hr_Permission=@Hr_Permission
  where User_Id=@User_Id and Id=@Id
    end
else if(@Actiontype='Select2')
    begin
  select * from tbl_Attendance where User_Id=@User_Id and cast(Coming_Time as Date) = cast(getdate() as Date)
     end

else if(@Actiontype='Select3')
    begin
  select * from tbl_Attendance where User_Id=@User_Id and cast(Coming_Time as Date) = cast(@ByDate as Date)
     end
 else if(@Actiontype='update3')
 begin
     update tbl_Attendance
  SET Coming_Time=@Coming_Time,Going_Time=@Going_Time WHERE  User_Id=@User_Id and cast(Coming_Time as Date) = cast(@ByDate as Date)
 end
else if(@Actiontype='Select4')
    begin
  select * from tbl_Attendance where User_Id=@User_Id
     end
else if(@Actiontype='Select5')
    begin
  select * from tbl_Attendance where cast(Coming_Time as Date) between cast(@StartDate as Date) and cast(@EndDate as Date) and User_Id=@User_Id
     end
else if(@Actiontype='select6')
 begin
     select DATEADD(dd, 0, DATEDIFF(dd, 0, Coming_Time)) as Coming_Time,User_Id,Id,Status,Coming_Time  Coming_Time1,Going_Time as Going_Time1 from tbl_Attendance where User_Id=@User_Id
 end
else if(@Actiontype='select7')
 begin
     select DATEADD(dd, 0, DATEDIFF(dd, 0, Coming_Time)) as Coming_Time,Id,User_Id,Id,Status from tbl_Attendance where User_Id=@User_Id and cast(Coming_Time as Date) between cast(@StartDate as Date) and cast(@EndDate as Date)
 end
else if(@Actiontype='select8')
 begin
     select DATEADD(dd, 0, DATEDIFF(dd, 0, Coming_Time)) as Coming_Time,Id,User_Id,Id,Status from tbl_Attendance where cast(Coming_Time as Date) = cast(@StartDate as Date)
 end

 else if(@Actiontype='nightU')
 begin
 if(exists(select 1 from tbl_Attendance where User_Id=@User_Id and cast(Coming_Time as Date) = cast(@ByDate as Date)))
 begin
     update tbl_Attendance
  SET Going_Time=@Going_Time WHERE  User_Id=@User_Id and cast(Coming_Time as Date) = cast(@ByDate as Date)
  end
 end


 else if(@Actiontype='Insert1')

  begin
       insert into tbl_Attendance(User_Id,Coming_Time,Going_Time,Coming_Message,Going_Message,Status,isMeetingGoodMorning)
       values (@User_Id,@Coming_Time,@Going_Time,@Coming_Message,@Going_Message,@Status,@isMeetingGoodMorning)
  end

  else if(@Actiontype='update4')
 begin
     update tbl_Attendance
  SET Coming_Time=@Coming_Time,Going_Time=@Going_Time,Status=@Status,Coming_Message=@Coming_Message,Going_Message=@Going_Message WHERE  User_Id=@User_Id and cast(Coming_Time as Date) = cast(@ByDate as Date)
 end

 else if(@Actiontype='Select11')
    begin
  select a.*,b.name from tbl_Attendance a inner join tbl_Login b on a.User_Id=b.srno where cast(Coming_Time as date) = cast(getdate() as date) and a.Status='Go' 
  union
  select a.*,b.name from tbl_Attendance a inner join tbl_Login b on a.User_Id=b.srno where  cast(Coming_Time as datetime) >= cast(getdate()-31 as datetime)
   and cast(Coming_Time as datetime)!=cast(Going_Time as datetime)
   and Hr_Permission=0 and a.Status='Come' order by b.name asc 
  end  
 else if(@Actiontype='UpdateHr')
    begin
    update tbl_Attendance
 SET Hr_Permission='1',Status='Go'
  WHERE  Id=@Id
  end  

  else if(@Actiontype='PendHrUp')
    begin
     if exists(select 1 from tbl_Attendance where User_Id=@User_Id and  cast(Coming_Time as date)=cast(getDate() as date))
begin
       update tbl_Attendance SET Hr_Permission='1' where User_Id=@User_Id and
    cast(Coming_Time as date)=cast(getDate() as date)
end
    
  end 


 end