
create PROCEDURE [dbo].[_2900_o_tarif_pakage_for_all_bimeh]
-- By Alich NeedSoft 1399/03/11

--  exec _2900_o_tarif_pakage_for_all_bimeh  '','','','','','','','','10','','','','','','','','','','','','','','',''

-- پارامتر های ورودی 
(@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 @s_mess nvarchar(4000) = '';
  DECLARE @number_inserted int = 0;
  DECLARE @debug_mode bit = 0;


  SET @s_mess = @s_mess + ' @code_bimeh=' + LTRIM(@code_bimeh)

  DELETE Anbar_Itm_Set_Masraf_Darman
  WHERE code_darman NOT IN (SELECT
      code_darman
    FROM darmanha
    WHERE code_Bimeh = @code_bimeh)

  -- select *  from Anbar_Itm_Set_Masraf_Darman where code_darman not in (select code_darman from darmanha where code_Bimeh=@code_bimeh)

  IF @debug_mode = 1
    SELECT
      @@ROWCOUNT

  IF @debug_mode = 1
    SELECT
      'con1'

  IF LEN(@code_bimeh) <> 0
  BEGIN
    IF EXISTS (SELECT TOP 1
        code_darman
      FROM Anbar_Itm_Set_Masraf_Darman
      GROUP BY code_darman)

    BEGIN

      IF @debug_mode = 1
        SELECT
          'con2'

      IF EXISTS (SELECT
          code_darman,
          code_ertebat
        FROM darmanha
        WHERE code_bimeh = @code_bimeh
        AND LEN(LTRIM(RTRIM(code_ertebat))) > 0
        AND code_darman IN (SELECT
          code_darman
        FROM Anbar_Itm_Set_Masraf_Darman
        GROUP BY code_darman))
      BEGIN

        DECLARE @code_darman_darmanha int
        DECLARE @code_ertebat_darmanha nvarchar(15)
        DECLARE @code_darman_past int

        DECLARE @radif_masraf int
        DECLARE @Code_Kala_masraf int
        DECLARE @Code_Unit_masraf int
        DECLARE @qout_masraf int
        DECLARE @fi_masraf int
        DECLARE @Jam_masraf int

        DECLARE cursor_darmanha CURSOR FAST_FORWARD FOR
        SELECT
          code_darman,
          code_ertebat
        FROM darmanha
        WHERE code_bimeh = @code_bimeh
        AND LEN(LTRIM(RTRIM(code_ertebat))) > 0
        AND code_darman IN (SELECT
          code_darman
        FROM Anbar_Itm_Set_Masraf_Darman
        GROUP BY code_darman)
        ORDER BY code_darman
        OPEN cursor_darmanha
        FETCH NEXT FROM cursor_darmanha INTO @code_darman_darmanha, @code_ertebat_darmanha
        -- loop 
        WHILE @@FETCH_STATUS = 0
        BEGIN

          -- SELECT
          --  @code_darman_past=code_darman
          --FROM darmanha
          --WHERE code_ertebat = @code_ertebat_darmanha


          DECLARE cursor_darmanha_past CURSOR FAST_FORWARD FOR
          SELECT
            code_darman
          FROM darmanha
          WHERE code_bimeh <> @code_bimeh
          AND code_ertebat = @code_ertebat_darmanha
    	  OPEN cursor_darmanha_past
		  FETCH NEXT FROM cursor_darmanha_past INTO @code_darman_past
          WHILE @@FETCH_STATUS = 0
          BEGIN


            IF @debug_mode = 1
            BEGIN
              SELECT
                'con3'
              SELECT
                'select top 1 id from Anbar_Itm_Set_Masraf_Darman where code_darman=' + LTRIM(@code_darman_past)
            END

            --delete  Anbar_Itm_Set_Masraf_Darman where code_darman= @code_darman_past

            IF NOT EXISTS (SELECT TOP 1
                id
              FROM Anbar_Itm_Set_Masraf_Darman
              WHERE code_darman = @code_darman_past)
            BEGIN
              -- begin inner loop 
              DECLARE cursor_masraf CURSOR FAST_FORWARD FOR
              SELECT
                radif,
                Code_Kala,
                Code_Unit,
                qout,
                fi,
                Jam
              FROM Anbar_Itm_Set_Masraf_Darman
              WHERE code_darman = @code_darman_darmanha

              OPEN cursor_masraf
              FETCH NEXT FROM cursor_masraf INTO @radif_masraf, @Code_Kala_masraf, @Code_Unit_masraf, @qout_masraf, @fi_masraf, @Jam_masraf

              IF @debug_mode = 1
                SELECT
                  'con4'


              WHILE @@FETCH_STATUS = 0
              BEGIN

                INSERT INTO Anbar_Itm_Set_Masraf_Darman (code_darman
                , radif
                , Code_Kala
                , Code_Unit
                , kardex
                , qin
                , qout
                , Fi
                , Jam
                , code_pezeshk
                , Insert_Time)
                  VALUES (@code_darman_past, @radif_masraf, @Code_Kala_masraf, @Code_Unit_masraf, 1, 0, @qout_masraf, @fi_masraf, @Jam_masraf, 0, GETDATE())

                IF @@ROWCOUNT = 1
                  SET @number_inserted = @number_inserted + 1





                FETCH NEXT FROM cursor_masraf INTO @radif_masraf, @Code_Kala_masraf, @Code_Unit_masraf, @qout_masraf, @fi_masraf, @Jam_masraf
              END

              CLOSE cursor_masraf
              DEALLOCATE cursor_masraf
            END


            FETCH NEXT FROM cursor_darmanha_past INTO @code_darman_past
          END
          CLOSE cursor_darmanha_past
          DEALLOCATE cursor_darmanha_past

          -- end inner loop

          FETCH NEXT FROM cursor_darmanha INTO @code_darman_darmanha, @code_ertebat_darmanha
        END
        CLOSE cursor_darmanha
        DEALLOCATE cursor_darmanha
        -- end outter loop
        SELECT
          STR(@number_inserted) AS s_result,
          '(: Number of Inserted to Anbar_Itm_Set_Masraf_Darman  with :   ' + OBJECT_NAME(@@PROCID) + '  ' + STR(@number_inserted) AS s_message,
          @s_mess AS s_param
      END
      ELSE
        SELECT
          '0' AS s_result,
          '): no record in table darmanha  stored procedure :   ' + OBJECT_NAME(@@PROCID) AS s_message,
          @s_mess AS s_param

    END
    ELSE
      SELECT
        '0' AS s_result,
        '): no record in table Anbar_Itm_Set_Masraf_Darman stored procedure :   ' + OBJECT_NAME(@@PROCID) AS s_message,
        @s_mess AS s_param
  END
  ELSE
    SELECT
      '0' AS s_result,
      '): no base code_bimeh for copy stored procedure    کد بیمه مبنا مشخص نشده   ' + OBJECT_NAME(@@PROCID) AS s_message,
      @s_mess AS s_param

END

