/****** Object:  UserDefinedFunction [dbo].[T]    Script Date: 11/19/2023 2:20:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 create function [dbo].[T](@TimeAsInt int)
  returns varchar(5)
   AS 
   Begin
    return(Format(@TimeAsInt / 60,'00') + ':' + Format(@TimeAsInt % 60,'00'))
  End
GO
/****** Object:  StoredProcedure [dbo].[sp_CalcTimeCtrlAccount]    Script Date: 11/19/2023 2:20:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_CalcTimeCtrlAccount]
AS
BEGIN
DECLARE @BeginDate dateTime,@BeginTime int,@EndTime int
DECLARE  DayList CURSOR FAST_FORWARD
   FOR Select * from #ShiftDetail
 OPEN DayList
  FETCH NEXT FROM DayList INTO @BeginDate,@BeginTime,@EndTime
 WHILE @@FETCH_STATUS = 0
  BEGIN

/*#########*/ DECLARE @FirstTime int,@SecTime int ,@TimeType tinyint,@InOrg bit
/*#########*/ DECLARE  CalcList CURSOR FAST_FORWARD
/*#########*/    FOR Select FirstTime ,SecTime ,CardType ,InOrg  from 
/*#########*/         #TimeCards t WHERE t.BeginDate = @BeginDate
/*#########*/  OPEN CalcList
/*#########*/   FETCH NEXT FROM CalcList INTO @FirstTime,@SecTime,@TimeType,@InOrg
/*#########*/  WHILE @@FETCH_STATUS = 0
/*#########*/   BEGIN
/*#########*/ 
/*#########*/     Select @FirstTime,@SecTime,@TimeType,@InOrg
/*#########*/ 
/*#########*/ 
/*#########*/     FETCH NEXT FROM CalcList INTO @FirstTime,@SecTime,@TimeType,@InOrg
/*#########*/   END
/*#########*/  CLOSE CalcList
/*#########*/  DEALLOCATE CalcList
   
       Select @BeginDate,@BeginTime,@EndTime
     
    FETCH NEXT FROM DayList INTO @BeginDate,@BeginTime,@EndTime
  END
 CLOSE DayList
 DEALLOCATE DayList
END
GO
/****** Object:  StoredProcedure [dbo].[sp_CalcTimeCtrlAccountData]    Script Date: 11/19/2023 2:20:01 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[sp_CalcTimeCtrlAccountData]
AS
BEGIN

If (OBJECT_ID('tempdb..#ShiftDetail') IS NOT NULL) Drop Table #ShiftDetail
Create Table #ShiftDetail(BeginDate dateTime,BeginTime int,EndTime int)
Insert Into #ShiftDetail(BeginDate,BeginTime,EndTime)
 Values
    ('2023-11-19',8 * 60  , 17 * 60),
	('2023-11-20',8 * 60  , 17 * 60),    
	('2023-11-21',8 * 60  , 17 * 60)    

If (OBJECT_ID('tempdb..#TimeCtrl') IS NOT NULL) Drop Table #TimeCtrl
Create Table #TimeCtrl(Id int Identity(1,1) Primary key,CardDate dateTime,CardTime int,CardType tinyint)
Insert Into #TimeCtrl(CardDate,CardTime,CardType)
 Values
    ('2023-11-19',9 * 60  , 1) ,('2023-11-19',11 * 60  , 1) ,('2023-11-19',12 * 60  , 1) ,('2023-11-19',18 * 60  , 1) ,
	('2023-11-20',8 * 60  , 1) ,('2023-11-19',16 * 60  , 1) 
 

If (OBJECT_ID('tempdb..#Demand') IS NOT NULL) Drop Table #Demand
Create Table #Demand(DemandDate dateTime,FromTime int,ToTime int,DemandType tinyint)
Insert Into #Demand(DemandDate,FromTime,ToTime,DemandType)
 Values
    ('2023-11-19',8 * 60  , 9 * 60 , 1),/*leave*/
    ('2023-11-19',11 * 60 ,13 * 60 , 2),/*function*/

    ('2023-11-19',16 * 60 + 30 , 17 * 60 , 1)/*leave*/


If (OBJECT_ID('tempdb..#TimeCards') IS NOT NULL) Drop Table #TimeCards
Create Table #TimeCards(BeginDate dateTime,FirstTime int,SecTime int,CardType tinyint,InOrg bit)
Insert Into #TimeCards(BeginDate,FirstTime,SecTime,CardType,InOrg)
  Select T1.CardDate,T1.CardTime,T2.CardTime,T1.CardType,Cast(T1.Id % 2 as bit)
   from #TimeCtrl T1
      Inner Join #TimeCtrl T2   ON T1.Id + 1 = T2.Id 


   EXEC sp_CalcTimeCtrlAccount
END