
use [Darmangah]
Go

declare @old_name varchar(200)
IF EXISTS (SELECT name FROM sysobjects
     WHERE name = 'Fn_String_descr_Attendance' AND type = 'FN')
	 begin
	 set @old_name='Fn_String_descr_Attendance'+'_old_'+[dbo].[Fn_Now_DateTime]()
	  EXEC sp_rename 'Fn_String_descr_Attendance',@old_name;
   end
Go

--------------
create FUNCTION [dbo].[Fn_String_descr_Attendance]
-- select [dbo].[Fn_String_descr_Attendance](1,'1399/02/11')
(@cp int,@sdate char(10) )
RETURNS nvarchar(1000)
AS
BEGIN
    DECLARE @Result nvarchar(1000)='';
	DECLARE @String_descr nvarchar(1000)='';
	DECLARE @descr nvarchar(50)='';
	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 atten_Cursor CURSOR FAST_FORWARD
			FOR
			SELECT sharh
			FROM In_Out_Person
			WHERE Code_Person=@cp and tarikh=@sdate order by tarikh,Saat

			OPEN atten_Cursor

			FETCH NEXT FROM atten_Cursor INTO @descr
		
			WHILE @@FETCH_STATUS = 0
			BEGIN

			       if @descr is null
				   set   @descr=''
			        
					if @i%2=0
					set @String_descr=@String_descr+@descr+' - '
					else
					set @String_descr=@String_descr+@descr+'    '
					set @i=@i+1

					FETCH NEXT FROM atten_Cursor INTO @descr
			END

			CLOSE atten_Cursor
			DEALLOCATE atten_Cursor 

			set @Result=@String_descr

	end
	else
	set @Result=''


	RETURN @Result

end
/*


Total attendance
*/