
USE [Darmangah]
Go



declare @old_name varchar(200)
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'Fn_Sum_Attendance' AND type = 'FN')
	 begin
	 set @old_name='Fn_Sum_Attendance'+'_old_'+[dbo].[Fn_Now_DateTime]()
	  EXEC sp_rename 'Fn_Sum_Attendance',@old_name;
   end
Go
--------------
CREATE FUNCTION [dbo].[Fn_Sum_Attendance]
(@cp int,@sdate char(10) )
-- select [dbo].[Fn_Sum_Attendance](1,'1399/02/11')
-- select [dbo].[Fn_Sum_Attendance](2,'1399/02/11')
-- select * from In_Out_Person
RETURNS int
AS
BEGIN
    DECLARE @Result int=0;
	DECLARE @R_Count int=0;
	DECLARE @stime char(5)='00:00';
	DECLARE @stime1 char(5)='00:00';
	DECLARE @stime2 char(5)='00:00';
	DECLARE @S_A int=0;

	select @R_Count=COUNT(*) from In_Out_Person where Code_Person=@cp and tarikh=@sdate

	--تعداد تراکنش ها زوج باشد 
	if (@R_Count>=2)and(@R_Count%2=0)
	begin



        DECLARE In_Out_Person_Cursor CURSOR FAST_FORWARD
        FOR
            SELECT Saat
            from In_Out_Person where Code_Person=@cp and tarikh=@sdate  order by tarikh,Saat

        OPEN In_Out_Person_Cursor

        FETCH NEXT FROM In_Out_Person_Cursor INTO @stime
		set @stime1=@stime

        WHILE @@FETCH_STATUS = 0
            BEGIN
             
                 FETCH NEXT FROM In_Out_Person_Cursor INTO @stime
				 set @stime2=@stime

				 set @S_A=@S_A+DATEDIFF(MINUTE,@stime1,@stime2)  

                 FETCH NEXT FROM In_Out_Person_Cursor INTO @stime
				 set @stime1=@stime

            END


        CLOSE In_Out_Person_Cursor
        DEALLOCATE In_Out_Person_Cursor 


	set @Result=@S_A

	end
	else
	set @Result=0

	RETURN @Result

end

GO


