USE [Darmangah]
Go



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


Create PROCEDURE [dbo].[_2035_r_atten_all_per_sum_hours_period_01] (
-- By Alich NeedSoft 1399/02/14
-- exec _2035_r_atten_all_per_sum_hours_period_01  '1399/02/09','1399/02/12','','','','','','','','','','','','','','','','2','','','','','',''
@fday char(10),
@tday char(10),
@ftime char(20),
@ttime char(20),
@code_user int,
@code_shift int,
@code_shobeh int,
@code_pay int,
@code_bimeh int,
@code_bakhsh int,
@code_pezeshk int,
@code_dastyar int,
@code_darman int,
@code_bimar int,
@code_ranandeh int,
@code_yaraneh int,
@code_mokammel int,
@sh_kargozini varchar(20),
@code_ertebat varchar(20),
@code_shift_str varchar(500),
@code_bimeh_str varchar(500),
@code_bakhsh_str varchar(500),
@code_pezeshk_str varchar(500),
@lucp int)
AS
BEGIN
  SET @fday = RTRIM(LTRIM(@fday))
  SET @tday = RTRIM(LTRIM(@tday))
  SET @ftime = RTRIM(LTRIM(@ftime))
  SET @ttime = RTRIM(LTRIM(@ttime))
  SET @sh_kargozini = RTRIM(LTRIM(@sh_kargozini))
  SET @code_ertebat = RTRIM(LTRIM(@code_ertebat))
  SET @code_shift_str = RTRIM(LTRIM(@code_shift_str))
  SET @code_bimeh_str = RTRIM(LTRIM(@code_bimeh_str))
  SET @code_bakhsh_str = RTRIM(LTRIM(@code_bakhsh_str))
  SET @code_pezeshk_str = RTRIM(LTRIM(@code_pezeshk_str))

  DECLARE @daysy char(10) = ''
  DECLARE @num_daysy char(2)
  DECLARE @c_cp int = 0
  DECLARE @np nvarchar(50)
  DECLARE @Sum_Houre char(10)
  DECLARE @Sum_Minute int = 0

  DECLARE @temp TABLE (
    cp int,
    np nvarchar(50),
    Sum_Minute int,
    Sum_Houre char(10)
  )

  IF (LEN(@fday) = 10)
    AND (LEN(@tday) = 10)
    AND (@tday >= @fday)
  BEGIN

    DECLARE In_Out_Person_Cursor CURSOR FAST_FORWARD FOR
    SELECT
      Code_Person
    FROM In_Out_Person
    WHERE tarikh BETWEEN @fday AND @tday
    GROUP BY Code_Person
    ORDER BY Code_Person
    OPEN In_Out_Person_Cursor
    FETCH NEXT FROM In_Out_Person_Cursor INTO @c_cp
    WHILE @@FETCH_STATUS = 0
    BEGIN
     --select @c_cp
      DECLARE tarikh_Cursor CURSOR FAST_FORWARD FOR
      SELECT
        tarikh
      FROM tarikh
      WHERE tarikh BETWEEN @fday AND @tday
      ORDER BY tarikh
      OPEN tarikh_Cursor
      FETCH NEXT FROM tarikh_Cursor INTO @daysy
      WHILE @@FETCH_STATUS = 0
      BEGIN
        -- SET @num_daysy = SUBSTRING(@daysy, 9, 2)
        -- SET @Sum_Houre = LTRIM(RTRIM([dbo].[FN_MinutesToDuration]([dbo].[Fn_Sum_Attendance](@c_cp, @daysy))))
        --select @daysy,@num_daysy,@Sum_Houre
        SET @Sum_Minute = @Sum_Minute + [dbo].[Fn_Sum_Attendance](@c_cp, @daysy)

        FETCH NEXT FROM tarikh_Cursor INTO @daysy
      END
      CLOSE tarikh_Cursor
      DEALLOCATE tarikh_Cursor

      SELECT
        @np = name_moshtari
      FROM moshtari
      WHERE code_moshtari = @c_cp

      SET @Sum_Houre = LTRIM(RTRIM([dbo].[FN_MinutesToDuration](@Sum_Minute)))

      INSERT @temp (cp, np, Sum_Minute, Sum_Houre)
        VALUES (@c_cp, @np, @Sum_Minute, @Sum_Houre)

      SET @Sum_Minute = 0

      FETCH NEXT FROM In_Out_Person_Cursor INTO @c_cp
    END
    CLOSE In_Out_Person_Cursor
    DEALLOCATE In_Out_Person_Cursor

    SELECT
      *
    FROM @temp
  END
  ELSE
    SELECT
      0 AS s_result,
      '  no condition   لطفا محدوده تاریخ صحیح وارد نمایید       ' AS s_meesage,
      '  no param' AS s_param

END


Go



