
USE [Darmangah]
Go



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

ALTER procedure [dbo].[_2032_r_atten_matris_date_hours_01]
-- exec _2032_r_atten_matris_date_hours_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 @SQL_ST varchar(200)

  DECLARE @D01 char(10) = ''
  DECLARE @D02 char(10) = ''
  DECLARE @D03 char(10) = ''
  DECLARE @D04 char(10) = ''
  DECLARE @D05 char(10) = ''
  DECLARE @D06 char(10) = ''
  DECLARE @D07 char(10) = ''
  DECLARE @D08 char(10) = ''
  DECLARE @D09 char(10) = ''
  DECLARE @D10 char(10) = ''
  DECLARE @D11 char(10) = ''
  DECLARE @D12 char(10) = ''
  DECLARE @D13 char(10) = ''
  DECLARE @D14 char(10) = ''
  DECLARE @D15 char(10) = ''
  DECLARE @D16 char(10) = ''
  DECLARE @D17 char(10) = ''
  DECLARE @D18 char(10) = ''
  DECLARE @D19 char(10) = ''
  DECLARE @D20 char(10) = ''
  DECLARE @D21 char(10) = ''
  DECLARE @D22 char(10) = ''
  DECLARE @D23 char(10) = ''
  DECLARE @D24 char(10) = ''
  DECLARE @D25 char(10) = ''
  DECLARE @D26 char(10) = ''
  DECLARE @D27 char(10) = ''
  DECLARE @D28 char(10) = ''
  DECLARE @D29 char(10) = ''
  DECLARE @D30 char(10) = ''
  DECLARE @D31 char(10) = ''



  DECLARE @temp TABLE (
    cp int,
    np nvarchar(50),
    D01 char(10),
    D02 char(10),
    D03 char(10),
    D04 char(10),
    D05 char(10),
    D06 char(10),
    D07 char(10),
    D08 char(10),
    D09 char(10),
    D10 char(10),
    D11 char(10),
    D12 char(10),
    D13 char(10),
    D14 char(10),
    D15 char(10),
    D16 char(10),
    D17 char(10),
    D18 char(10),
    D19 char(10),
    D20 char(10),
    D21 char(10),
    D22 char(10),
    D23 char(10),
    D24 char(10),
    D25 char(10),
    D26 char(10),
    D27 char(10),
    D28 char(10),
    D29 char(10),
    D30 char(10),
    D31 char(10)
  )






    if ( (LEN(@fday)=10) and (LEN(@tday)=10))
	begin


	    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)


      IF @num_daysy = '01'
        SET @D01 = @daysy

      IF @num_daysy = '02'
        SET @D02 = @daysy

      IF @num_daysy = '03'
        SET @D03 = @daysy

      IF @num_daysy = '04'
        SET @D04 = @daysy

      IF @num_daysy = '05'
        SET @D05 = @daysy

      IF @num_daysy = '06'
        SET @D06 = @daysy

      IF @num_daysy = '07'
        SET @D07 = @daysy

      IF @num_daysy = '08'
        SET @D08 = @daysy

      IF @num_daysy = '09'
        SET @D09 = @daysy

      IF @num_daysy = '10'
        SET @D10 = @daysy

      IF @num_daysy = '11'
        SET @D11 = @daysy

      IF @num_daysy = '12'
        SET @D12 = @daysy

      IF @num_daysy = '13'
        SET @D13 = @daysy

      IF @num_daysy = '14'
        SET @D14 = @daysy

      IF @num_daysy = '15'
        SET @D15 = @daysy

      IF @num_daysy = '16'
        SET @D16 = @daysy

      IF @num_daysy = '17'
        SET @D17 = @daysy

      IF @num_daysy = '18'
        SET @D18 = @daysy

      IF @num_daysy = '19'
        SET @D19 = @daysy

      IF @num_daysy = '20'
        SET @D20 = @daysy

      IF @num_daysy = '21'
        SET @D21 = @daysy

      IF @num_daysy = '22'
        SET @D22 = @daysy

      IF @num_daysy = '23'
        SET @D23 = @daysy

      IF @num_daysy = '24'
        SET @D24 = @daysy

      IF @num_daysy = '25'
        SET @D25 = @daysy

      IF @num_daysy = '26'
        SET @D26 = @daysy

      IF @num_daysy = '27'
        SET @D27 = @daysy

      IF @num_daysy = '28'
        SET @D28 = @daysy

      IF @num_daysy = '29'
        SET @D29 = @daysy

      IF @num_daysy = '30'
        SET @D30 = @daysy

      IF @num_daysy = '31'
        SET @D31 = @daysy


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



    INSERT @temp (cp, np, D01, D02, D03, D04, D05, D06, D07, D08, D09, D10, D11, D12, D13, D14, D15, D16, D17, D18, D19, D20, D21, D22, D23, D24, D25, D26, D27, D28, D29, D30, D31)
      VALUES (0, '', @D01, @D02, @D03, @D04, @D05, @D06, @D07, @D08, @D09, @D10, @D11, @D12, @D13, @D14, @D15, @D16, @D17, @D18, @D19, @D20, @D21, @D22, @D23, @D24, @D25, @D26, @D27, @D28, @D29, @D30, @D31)




	 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

        IF @num_daysy = '01'
          SET @D01 = @Sum_Houre

        IF @num_daysy = '02'
          SET @D02 = @Sum_Houre

        IF @num_daysy = '03'
          SET @D03 = @Sum_Houre

        IF @num_daysy = '04'
          SET @D04 = @Sum_Houre

        IF @num_daysy = '05'
          SET @D05 = @Sum_Houre

        IF @num_daysy = '06'
          SET @D06 = @Sum_Houre

        IF @num_daysy = '07'
          SET @D07 = @Sum_Houre

        IF @num_daysy = '08'
          SET @D08 = @Sum_Houre

        IF @num_daysy = '09'
          SET @D09 = @Sum_Houre

        IF @num_daysy = '10'
          SET @D10 = @Sum_Houre

        IF @num_daysy = '11'
          SET @D11 = @Sum_Houre

        IF @num_daysy = '12'
          SET @D12 = @Sum_Houre

        IF @num_daysy = '13'
          SET @D13 = @Sum_Houre

        IF @num_daysy = '14'
          SET @D14 = @Sum_Houre

        IF @num_daysy = '15'
          SET @D15 = @Sum_Houre

        IF @num_daysy = '16'
          SET @D16 = @Sum_Houre

        IF @num_daysy = '17'
          SET @D17 = @Sum_Houre

        IF @num_daysy = '18'
          SET @D18 = @Sum_Houre

        IF @num_daysy = '19'
          SET @D19 = @Sum_Houre

        IF @num_daysy = '20'
          SET @D20 = @Sum_Houre

        IF @num_daysy = '21'
          SET @D21 = @Sum_Houre

        IF @num_daysy = '22'
          SET @D22 = @Sum_Houre

        IF @num_daysy = '23'
          SET @D23 = @Sum_Houre

        IF @num_daysy = '24'
          SET @D24 = @Sum_Houre

        IF @num_daysy = '25'
          SET @D25 = @Sum_Houre

        IF @num_daysy = '26'
          SET @D26 = @Sum_Houre

        IF @num_daysy = '27'
          SET @D27 = @Sum_Houre

        IF @num_daysy = '28'
          SET @D28 = @Sum_Houre

        IF @num_daysy = '29'
          SET @D29 = @Sum_Houre

        IF @num_daysy = '30'
          SET @D30 = @Sum_Houre

        IF @num_daysy = '31'
          SET @D31 = @Sum_Houre




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

      --select @D01,@D02,@D03,@D04,@D05,@D06,@D07,@D08,@D09,@D10,@D11,@D12,@D13,@D14,@D15,@D16,@D17,@D18,@D19,@D20,@D21,@D22,@D23,@D24,@D25,@D26,@D27,@D28,@D29,@D30,@D31

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

      INSERT @temp (cp, np, D01, D02, D03, D04, D05, D06, D07, D08, D09, D10, D11, D12, D13, D14, D15, D16, D17, D18, D19, D20, D21, D22, D23, D24, D25, D26, D27, D28, D29, D30, D31)
        VALUES (@c_cp, @np, @D01, @D02, @D03, @D04, @D05, @D06, @D07, @D08, @D09, @D10, @D11, @D12, @D13, @D14, @D15, @D16, @D17, @D18, @D19, @D20, @D21, @D22, @D23, @D24, @D25, @D26, @D27, @D28, @D29, @D30, @D31)


      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 to run sp : '+OBJECT_NAME(@@PROCID) as s_message ,' no param '+OBJECT_NAME(@@PROCID) as s_param


end
GO

INSERT [dbo].[users_permission] ([cp], [c_permission]) VALUES (1, 2032)
GO
INSERT [dbo].[users_permission_itm] ([c_permission], [n_permission], [descr], [act], [c_kind]) VALUES (2032, N'_2032_r_atten_matris_date_hours_01', N'گزارش تاریخ یک ماهه کلیه اشخاص با ساعت کارکرد', 1, 11)

Go




