
USE [Darmangah]
Go



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

	select @R_Count=COUNT(*) from In_Out_Person where Code_Person=@cp and tarikh=@sdate
	if (@R_Count>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
		
			WHILE @@FETCH_STATUS = 0
			BEGIN
					if @i%2=0
					set @S_A_S=@S_A_S+@stime+' - '
					else
					set @S_A_S=@S_A_S+@stime+'    '
					set @i=@i+1

					FETCH NEXT FROM In_Out_Person_Cursor INTO @stime
			END

			CLOSE In_Out_Person_Cursor
			DEALLOCATE In_Out_Person_Cursor 

			set @Result=@S_A_S

	end
	else
	set @Result=''


	RETURN @Result

end
/*


Total attendance
*/
GO


