

create PROCEDURE [dbo].[_2800_r_acc_itm_sepidar_hamkaran_system]
-- By Alich NeedSoft 1399/03/15

--  exec _2800_r_acc_itm_sepidar_hamkaran_system  '','','','','','','','','','','','','','','','','','','','','','','',''
--  exec _2800_r_acc_itm_sepidar_hamkaran_system  '1397/01/01','1399/04/04','','','','','','','','','','','','','','','','','','','','','',''

  -- ایجاد محدودیت  برای برخی از رکوردها که در سرجمع محاسبه نشون
/*

ALTER TABLE [dbo].[morajeat]
ADD  [acc_itm] [bit] NULL  CONSTRAINT [DF_morajeat_acc_itm]  DEFAULT ((1))
GO
update morajeat set acc_itm=1 where acc_itm is null
Go

*/


-- پارامتر های ورودی 
(@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 @sql_filter nvarchar(4000) = '';
  DECLARE @sql_st nvarchar(4000) = '';
  DECLARE @Count_inserted int = 0;
  DECLARE @debug_mode bit =0;
  DECLARE @allow bit = 1;
  DECLARE @Sum_Acc_itm money = 0;

  IF NOT ((LEN(@fday) = 10)
    AND (LEN(@tday) = 10))
  BEGIN
    SET @allow = 0
    SET @s_mess = @s_mess + ' محدوده تاریخ صحیح نمی باشد  '
  END

  -- ایجاد جدول موقت جهت ثبت قلم های اسناد
  DECLARE @temp TABLE (
    id int IDENTITY (1, 1),
    code_kol varchar(20),
    code_goroh varchar(20),
    code_moein varchar(20),
    code_tafzil varchar(20),
    sharh_sanad nvarchar(1000),
    bed money,
    bes money,
    cp int,
    np nvarchar(100),
    sdate char(10),
    fday char(10),
    tday char(10)

  )
  -- ایجاد محدودیت  برای برخی از رکوردها که در سرجمع محاسبه نشون
/*
ALTER TABLE [dbo].[morajeat]
ADD  [acc_itm] [bit] NULL  CONSTRAINT [DF_morajeat_acc_itm]  DEFAULT ((1))
GO
update morajeat set acc_itm=1 where acc_itm is null
Go
*/
  SELECT
    @sql_filter = code_pay_adam_mohasebe_hesabdari
  FROM settingc

  if @sql_filter is null
  set @sql_filter=''

  if len(LTRIM(RTRIM(@sql_filter)))>0
  begin
  set @sql_st=' update  morajeat  set acc_itm=0 where 1>0 and tarikh between '+@fday+' and '+@tday + @sql_filter 
  exec(@sql_st)
  end

  IF @debug_mode = 1
    SELECT
      ' 0  '+@sql_st

  
  --1
  IF @debug_mode = 1
    SELECT
      ' 1     karkerd sarjame babat har pezeshk    سرجمع کارکرد بابت هر پزشک'
      -- karkerd sarjame babat har pezeshk    سرجمع کارکرد بابت هر پزشک

  DECLARE @cp_cur int = 0
  DECLARE @np_cur nvarchar(100) = ''
  DECLARE @code_kol_cur varchar(20) = ''
  DECLARE @code_goroh_cur varchar(20) = ''
  DECLARE @code_moein_cur varchar(20) = ''
  DECLARE @code_tafzil_cur varchar(20) = ''
  DECLARE @sharh_sanad_cur nvarchar(1000) = ''

  -- Anbar_Hdr
  -- تعریف متغیر برای کرسر  عنوان انبار 
   DECLARE @ID_Anbar_Hdr_cur int=0
   DECLARE @tarikh_Anbar_Hdr_cur char(10)=''
   DECLARE @jam_Anbar_Hdr_cur money=0
   DECLARE @code_tafzil_Anbar_Hdr_cur  char(20)


  -- Anbar_itm
  -- تعریف متغیر برای کرسر  قلم انبار 
   DECLARE   @Code_Kala_Anbar_itm_cur int=0 
   DECLARE   @jam_Anbar_itm_cur money=0



  -- تعریف متغییر ای کرسر  نوع پرداخت
  DECLARE @code_pay_cur int = 0
  DECLARE @sum_bes_pay_cur money = 0
  -- کارکرد بانکها
  DECLARE @code_bank_cur int = 0
  DECLARE @sum_mablagh_cur money = 0
  -- کارکرد بیمه ها
  DECLARE @code_bimeh_cur int = 0
  DECLARE @sum_bimeh_cur money = 0
  -- کارکرد پزشکان
  DECLARE @code_pezeshk_cur int = 0
  DECLARE @sum_bes_pezeshk_cur money = 0
  DECLARE @sum_bimeh_pezeshk_cur money = 0
  DECLARE @darsad_naghd_pezeshk_cur float = 0
  DECLARE @darsad_bimeh_pezeshk_cur float = 0
  DECLARE @sahme_naghd_pezeshk_cur money = 0
  DECLARE @sahme_bimeh_pezeshk_cur money = 0

  DECLARE cursor_sarjam_karkerd_pezeshkan CURSOR FAST_FORWARD FOR
  SELECT
    code_pezeshk,
    SUM(r_bes),
    SUM(r_bimeh)
  FROM morajeat
  WHERE tarikh BETWEEN @fday AND @tday and acc_itm=1
  GROUP BY code_pezeshk
  ORDER BY code_pezeshk
  OPEN cursor_sarjam_karkerd_pezeshkan
  FETCH NEXT FROM cursor_sarjam_karkerd_pezeshkan INTO @code_pezeshk_cur, @sum_bes_pezeshk_cur, @sum_bimeh_pezeshk_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN

    SET @cp_cur = @code_pezeshk_cur
    
	SELECT
      @np_cur = name_pezeshk
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
  
    SELECT
      @darsad_naghd_pezeshk_cur = darsad_naghd
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur
  
    SELECT
      @darsad_bimeh_pezeshk_cur = darsad_bimeh
    FROM pezeshkan
    WHERE code_pezeshk = @code_pezeshk_cur

    SET @sahme_naghd_pezeshk_cur = @sum_bes_pezeshk_cur * (@darsad_naghd_pezeshk_cur / 100)

    SET @sharh_sanad_cur = ' کارکرد ' + LTRIM(@darsad_naghd_pezeshk_cur) + '  درصد نقد پزشک  ' + @np_cur + ' کد ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday

    IF @sahme_naghd_pezeshk_cur > 0
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, 0, @sahme_naghd_pezeshk_cur, @fday, @fday, @tday)


    SET @sahme_bimeh_pezeshk_cur = @sum_bimeh_pezeshk_cur * (@darsad_bimeh_pezeshk_cur / 100)

    SET @sharh_sanad_cur = ' کارکرد ' + LTRIM(@darsad_bimeh_pezeshk_cur) + '  درصد بیمه پزشک  ' + @np_cur + ' کد ' + LTRIM(@cp_cur) + '  ' + @fday + ' ' + @tday

    IF @sahme_bimeh_pezeshk_cur > 0
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, 0, @sahme_bimeh_pezeshk_cur, @fday, @fday, @tday)


     SET @Sum_Acc_itm=@Sum_Acc_itm+@sahme_naghd_pezeshk_cur+@sahme_bimeh_pezeshk_cur


    FETCH NEXT FROM cursor_sarjam_karkerd_pezeshkan INTO @code_pezeshk_cur, @sum_bes_pezeshk_cur, @sum_bimeh_pezeshk_cur
  END
  CLOSE cursor_sarjam_karkerd_pezeshkan
  DEALLOCATE cursor_sarjam_karkerd_pezeshkan

  --2
		IF @debug_mode = 1
		SELECT
		'  2        سرجمع کارکرد کلیه پزشکان   '
		-- سرجمع کارکرد کلیه پزشکان

  if @Sum_Acc_itm > 0
  begin

      SET @cp_cur = 0
    SET   @np_cur = ' کارکرد کلیه پزشکان  '
  
    SELECT
      @code_kol_cur = code_kol_pezeshkan
    FROM settingc
    WHERE id = 1

    SELECT
      @code_goroh_cur = code_goroh_pezeshkan
    FROM settingc
     WHERE id = 1

    SELECT
      @code_moein_cur = code_moein_pezeshkan
    FROM settingc
     WHERE id = 1

    SELECT
      @code_tafzil_cur = code_tafzil_pezeshkan
    FROM settingc
      WHERE id = 1
 
 

    SET @sharh_sanad_cur = '  کارکرد کل پزشکان  ' +  ' از ' + @fday + ' تا ' + @tday

 
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, @Sum_Acc_itm, 0, @fday, @fday, @tday)




  end

  set @Sum_Acc_itm=0


  --3
		IF @debug_mode = 1
		SELECT
		' 3     karkerd sarjam babat har bimeh   سرجمع کارکرد بابت هر بیمه    '
		  -- karkerd sarjam babat har bimeh   سرجمع کارکرد بابت هر بیمه 


  DECLARE cursor_sarjam_karkerd_bimeh CURSOR FAST_FORWARD FOR
  SELECT
    code_bimeh,
    SUM(r_bimeh)
  FROM morajeat
  WHERE tarikh BETWEEN @fday AND @tday and acc_itm=1
  GROUP BY code_bimeh
  ORDER BY code_bimeh
  OPEN cursor_sarjam_karkerd_bimeh
  FETCH NEXT FROM cursor_sarjam_karkerd_bimeh INTO @code_bimeh_cur, @sum_bimeh_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN


    SET @cp_cur = @code_bimeh_cur
    
	SELECT
      @np_cur = name_bimeh
    FROM bimeha
    WHERE code_bimeh = @code_bimeh_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM bimeha
    WHERE code_bimeh = @code_bimeh_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM bimeha
    WHERE code_bimeh = @code_bimeh_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM bimeha
    WHERE code_bimeh = @code_bimeh_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM bimeha
    WHERE code_bimeh = @code_bimeh_cur
 

    SET @sharh_sanad_cur = ' کارکرد بیمه ' +  @np_cur +  ' کد ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday

 
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, @sum_bimeh_cur, 0, @fday, @fday, @tday)

		set @Sum_Acc_itm=@Sum_Acc_itm+@sum_bimeh_cur

      FETCH NEXT FROM cursor_sarjam_karkerd_bimeh INTO @code_bimeh_cur, @sum_bimeh_cur
  END
  CLOSE cursor_sarjam_karkerd_bimeh
  DEALLOCATE cursor_sarjam_karkerd_bimeh

  --4

    IF @debug_mode = 1
    SELECT
    ' 4    Variz_Be_Hesab_Banki   واریز به حساب بانکی'
     -- Variz_Be_Hesab_Banki   واریز به حساب بانکی

  DECLARE cursor_sum_mablagh_bank CURSOR FAST_FORWARD FOR
  SELECT
    code_bank,
    SUM(mablagh)
  FROM Variz_Be_Hesab_Banki
  WHERE tarikh BETWEEN @fday AND @tday
  GROUP BY code_bank
  ORDER BY code_bank
  OPEN cursor_sum_mablagh_bank
  FETCH NEXT FROM cursor_sum_mablagh_bank INTO @code_bank_cur, @sum_mablagh_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN


    SET @cp_cur = @code_bank_cur
    
	SELECT
      @np_cur = name_bank
    FROM bank
    WHERE code_bank = @code_bank_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM bank
    WHERE code_bank = @code_bank_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM bank
    WHERE code_bank = @code_bank_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM bank
    WHERE code_bank = @code_bank_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM bank
    WHERE code_bank = @code_bank_cur
 

    SET @sharh_sanad_cur = ' کارکرد پوز یا واریز ' +  @np_cur +  ' کد ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday

 
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, @sum_mablagh_cur, 0, @fday, @fday, @tday)

        set @Sum_Acc_itm=@Sum_Acc_itm+@sum_mablagh_cur

     FETCH NEXT FROM cursor_sum_mablagh_bank INTO @code_bank_cur, @sum_mablagh_cur
  END
  CLOSE cursor_sum_mablagh_bank
  DEALLOCATE cursor_sum_mablagh_bank



  --5
      IF @debug_mode = 1
    SELECT
      '   5       انواع پرداخت بجز پوز یا واریز'
      -- انواع پرداخت بجز پوز یا واریز
	  /*

	       SQL.Add(' select code_pay,sum(r_bes) as s_r_bes ');
     SQL.Add(' from morajeat  ');
     SQL.Add(' where tarikh between'''+F_T+''' and ''' +T_T+'''');

     if code_not_mohasebe_st <> '' then
     SQL.Add(code_not_mohasebe_st);

     SQL.Add(' group by code_pay order by code_pay ');

	   DECLARE @code_pay_cur int = 0
  DECLARE @sum_bes_pay_cur money = 0

	  */


  DECLARE cursor_sum_pay CURSOR FAST_FORWARD FOR
  SELECT
    code_pay,
    SUM(r_bes)
  FROM morajeat
  WHERE tarikh BETWEEN @fday AND @tday  and acc_itm=1
  GROUP BY code_pay
  ORDER BY code_pay
  OPEN cursor_sum_pay
  FETCH NEXT FROM cursor_sum_pay INTO @code_pay_cur, @sum_bes_pay_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN


    SET @cp_cur = @code_pay_cur
    
	SELECT
      @np_cur = name_pay
    FROM pay
    WHERE code_pay = @code_pay_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM pay
    WHERE code_pay = @code_pay_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM pay
    WHERE code_pay = @code_pay_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM pay
    WHERE code_pay = @code_pay_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM pay
    WHERE code_pay = @code_pay_cur
 

    SET @sharh_sanad_cur = '   کار کرد   ' +  @np_cur +  '  کد  ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday

      ---  مواردی بغیر از واریز
	  if (@code_pay_cur<>3)
	  begin
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, @sum_bes_pay_cur, 0, @fday, @fday, @tday)

        set @Sum_Acc_itm=@Sum_Acc_itm+@sum_mablagh_cur
		end


      FETCH NEXT FROM cursor_sum_pay INTO @code_pay_cur, @sum_bes_pay_cur
  END
  CLOSE cursor_sum_pay
  DEALLOCATE cursor_sum_pay


  --6

      IF @debug_mode = 1
    SELECT
       '  6   سرجمع کل بیمه ها و نقد بابت در امد کلینیک'
       -- sarjam kol bimeh va daryafti  سرجمع کا بیمه ها و نقد بابت در امد کلینیک


  if @Sum_Acc_itm > 0
  begin

      SET @cp_cur = 0
    SET   @np_cur = ' درآمد مرکز  '
  
    SELECT
      @code_kol_cur = code_kol_daramad
    FROM settingc
    WHERE id = 1

    SELECT
      @code_goroh_cur = code_goroh_daramad
    FROM settingc
     WHERE id = 1

    SELECT
      @code_moein_cur = code_moein_daramad
    FROM settingc
     WHERE id = 1

    SELECT
      @code_tafzil_cur = code_tafzil_daramad
    FROM settingc
      WHERE id = 1
  
    SET @sharh_sanad_cur = '    درآمد مرکز    ' +  ' از ' + @fday + ' تا ' + @tday
 
      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur, 0, @Sum_Acc_itm, @fday, @fday, @tday)


  end

  set @Sum_Acc_itm=0
  --7
		IF @debug_mode = 1
		SELECT
		'  7  انبار ورود کالا و طرف کالا     '

  DECLARE cursor_Anbar_hdr CURSOR FAST_FORWARD FOR
  SELECT
    ID_Anbar_Hdr,
    tarikh,
	jam,
    code_tafzil 
  FROM Anbar_hdr
  WHERE  Code_Noa_Anbar=1 and  tarikh BETWEEN @fday AND @tday
  ORDER BY ID_Anbar_Hdr
  OPEN cursor_Anbar_hdr
  FETCH NEXT FROM cursor_Anbar_hdr INTO @ID_Anbar_Hdr_cur, @tarikh_Anbar_Hdr_cur , @jam_Anbar_Hdr_cur ,@code_tafzil_Anbar_Hdr_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN
  
			--Begin inner cursor Anbar_itm

			DECLARE cursor_Anbar_itm CURSOR FAST_FORWARD FOR
			SELECT
				Code_Kala,
				jam
			FROM Anbar_Itm
			WHERE tarikh BETWEEN @fday AND @tday and ID_Anbar_Hdr=@ID_Anbar_Hdr_cur
			ORDER BY radif
			OPEN cursor_Anbar_itm
			FETCH NEXT FROM cursor_Anbar_itm INTO @Code_Kala_Anbar_itm_cur, @jam_Anbar_itm_cur 

			WHILE @@FETCH_STATUS = 0
			BEGIN

				SET @cp_cur = @Code_Kala_Anbar_itm_cur
    
				SELECT
				@np_cur = name_kala
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_kol_cur = code_kol
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_goroh_cur = code_goroh
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_moein_cur = code_moein
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_tafzil_cur = code_tafzil
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
 

				SET @sharh_sanad_cur = '   ورود کالا   ' +  @np_cur +  '  کد  ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday


				INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
				VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur,@jam_Anbar_itm_cur,0, @fday, @fday, @tday)

                set @Sum_Acc_itm=@Sum_Acc_itm+@jam_Anbar_itm_cur


				FETCH NEXT FROM cursor_Anbar_itm INTO @Code_Kala_Anbar_itm_cur, @jam_Anbar_itm_cur 
				END
				CLOSE cursor_Anbar_itm
				DEALLOCATE cursor_Anbar_itm

	 --End inner cursor  Anbar_itm



    -- cursor_Anbar_hdr 

    SET @cp_cur = @code_tafzil_Anbar_Hdr_cur
    
	SELECT
      @np_cur = name_moshtari
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
 

    SET @sharh_sanad_cur = '  طرف ورود کالا   ' +  @np_cur +  '  کد  ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday


      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur,0, @Sum_Acc_itm, @fday, @fday, @tday)

       
       -- cursor_Anbar_hdr 
      FETCH NEXT FROM cursor_Anbar_hdr INTO @ID_Anbar_Hdr_cur, @tarikh_Anbar_Hdr_cur , @jam_Anbar_Hdr_cur ,@code_tafzil_Anbar_Hdr_cur
  END
  CLOSE cursor_Anbar_hdr
  DEALLOCATE cursor_Anbar_hdr









--8
set @Sum_Acc_itm=0
		IF @debug_mode = 1
		SELECT
		'  8      خروج کالا و طرف کالا    '

  DECLARE cursor_Anbar_hdr CURSOR FAST_FORWARD FOR
  SELECT
    ID_Anbar_Hdr,
    tarikh,
	jam,
    code_tafzil 
  FROM Anbar_hdr
  WHERE  Code_Noa_Anbar=2 and  tarikh BETWEEN @fday AND @tday
  ORDER BY ID_Anbar_Hdr
  OPEN cursor_Anbar_hdr
  FETCH NEXT FROM cursor_Anbar_hdr INTO @ID_Anbar_Hdr_cur, @tarikh_Anbar_Hdr_cur , @jam_Anbar_Hdr_cur ,@code_tafzil_Anbar_Hdr_cur

  WHILE @@FETCH_STATUS = 0
  BEGIN
  
			--Begin inner cursor Anbar_itm

			DECLARE cursor_Anbar_itm CURSOR FAST_FORWARD FOR
			SELECT
				Code_Kala,
				jam
			FROM Anbar_Itm
			WHERE tarikh BETWEEN @fday AND @tday and ID_Anbar_Hdr=@ID_Anbar_Hdr_cur
			ORDER BY radif
			OPEN cursor_Anbar_itm
			FETCH NEXT FROM cursor_Anbar_itm INTO @Code_Kala_Anbar_itm_cur, @jam_Anbar_itm_cur 

			WHILE @@FETCH_STATUS = 0
			BEGIN

				SET @cp_cur = @Code_Kala_Anbar_itm_cur
    
				SELECT
				@np_cur = name_kala
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_kol_cur = code_kol
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_goroh_cur = code_goroh
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_moein_cur = code_moein
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
   
				SELECT
				@code_tafzil_cur = code_tafzil
				FROM Kala
				WHERE code_kala = @Code_Kala_Anbar_itm_cur
 

				SET @sharh_sanad_cur = '   خروج کالا   ' +  @np_cur +  '  کد  ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday


				INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
				VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur,@jam_Anbar_itm_cur,0, @fday, @fday, @tday)

                set @Sum_Acc_itm=@Sum_Acc_itm+@jam_Anbar_itm_cur


				FETCH NEXT FROM cursor_Anbar_itm INTO @Code_Kala_Anbar_itm_cur, @jam_Anbar_itm_cur 
				END
				CLOSE cursor_Anbar_itm
				DEALLOCATE cursor_Anbar_itm

	 --End inner cursor  Anbar_itm



    -- cursor_Anbar_hdr 

    SET @cp_cur = @code_tafzil_Anbar_Hdr_cur
    
	SELECT
      @np_cur = name_moshtari
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_kol_cur = code_kol
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_goroh_cur = code_goroh
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_moein_cur = code_moein
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
   
    SELECT
      @code_tafzil_cur = code_tafzil
    FROM moshtari
    WHERE Code_tafzil = @code_tafzil_Anbar_Hdr_cur
 

    SET @sharh_sanad_cur = '  طرف خروج کالا   ' +  @np_cur +  '  کد  ' + LTRIM(@cp_cur) + ' از ' + @fday + ' تا ' + @tday


      INSERT @temp (cp, np, code_kol, code_goroh, code_moein, code_tafzil, sharh_sanad, bed, bes, sdate, fday, tday)
        VALUES (@cp_cur, @np_cur, @code_kol_cur, @code_goroh_cur, @code_moein_cur, @code_tafzil_cur, @sharh_sanad_cur,0, @Sum_Acc_itm, @fday, @fday, @tday)

       
       -- cursor_Anbar_hdr 
      FETCH NEXT FROM cursor_Anbar_hdr INTO @ID_Anbar_Hdr_cur, @tarikh_Anbar_Hdr_cur , @jam_Anbar_Hdr_cur ,@code_tafzil_Anbar_Hdr_cur
  END
  CLOSE cursor_Anbar_hdr
  DEALLOCATE cursor_Anbar_hdr


    IF @debug_mode = 1
    SELECT
      ' 9  خروجی اس پی'
  -- 
  --خروجی اس پی  
  IF @allow = 1
  BEGIN
    SELECT
      *
    FROM @temp
    ORDER BY id
  END
  ELSE
    SELECT
      '0' AS s_result,
      '): no condition ' + CHAR(13) + @s_mess + CHAR(13) + OBJECT_NAME(@@PROCID) AS s_message,
      @s_mess AS s_param

END

/*
1
    /*
	سرجمع کارکرد هر پزشک
              کد های قدیمی در دلفی
    		  p_c_kol :=code_hesab('code_kol','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_goroh :=code_hesab('code_goroh','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_moein :=code_hesab('code_moein','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_tafzil :=code_hesab('code_tafzil','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_darsad_naghd:=code_hesab('darsad_naghd','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              sahme_naghd_pezeshk:=CurrToStr(Trunc(FieldByName('s_r_bes').AsCurrency*(StrToCurr(p_darsad_naghd))/100));
              sharh_sanad:=' کارکرد '+name_pezeshk_with_darsad(FieldByName('code_pezeshk').AsString,True)+' '+ F_T;
              Sarjame_Sanad:=Sarjame_Sanad+StrToCurr(sahme_naghd_pezeshk);
    
    
              p_c_kol_2 :=code_hesab('code_kol_2','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_goroh_2 :=code_hesab('code_goroh_2','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_moein_2 :=code_hesab('code_moein_2','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_c_tafzil_2 :=code_hesab('code_tafzil_2','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              p_darsad_bimeh:=code_hesab('darsad_bimeh','pezeshkan','code_pezeshk',FieldByName('code_pezeshk').AsString) ;
              sahme_bimeh_pezeshk:=CurrToStr(Trunc(FieldByName('s_r_bimeh').AsCurrency*(StrToCurr(p_darsad_bimeh))/100));
              sharh_sanad_2:=' کارکرد '+name_pezeshk_with_darsad(FieldByName('code_pezeshk').AsString,False)+' '+ F_T;
              Sarjame_Sanad:=Sarjame_Sanad+StrToCurr(Sahme_Bimeh_Pezeshk);
    
    */
2
          //--- سرجمع کلیه پزشکان
          Write_To_Tarce_File('karkerd sarjame kol pezeshkan');

          p_c_kol :=code_hesab('code_kol_pezeshkan','settingc','id','1') ;
          p_c_goroh :=code_hesab('code_goroh_pezeshkan','settingc','id','1') ;
          p_c_moein :=code_hesab('code_moein_pezeshkan','settingc','id','1') ;
          p_c_tafzil :=code_hesab('code_tafzil_pezeshkan','settingc','id','1') ;

3
     کارکرد بیمه ها 
      --  karkerd sarjam babat har bimeh
     SQL.Add(' select code_bimeh,sum(r_bimeh) as s_r_bimeh ');
     SQL.Add(' from morajeat  ');
     SQL.Add(' where tarikh between'''+F_T+''' and ''' +T_T+'''');

     if code_not_mohasebe_st <> '' then
     SQL.Add(code_not_mohasebe_st);

     SQL.Add(' group by code_bimeh order by code_bimeh ');
     Write_To_Tarce_File(SQL.Text);
     Open;


     while not Eof do
     begin

          p_c_kol :=code_hesab('code_kol','bimeha','code_bimeh',FieldByName('code_bimeh').AsString) ;
          p_c_goroh :=code_hesab('code_goroh','bimeha','code_bimeh',FieldByName('code_bimeh').AsString) ;
          p_c_moein :=code_hesab('code_moein','bimeha','code_bimeh',FieldByName('code_bimeh').AsString) ;
          p_c_tafzil :=code_hesab('code_tafzil','bimeha','code_bimeh',FieldByName('code_bimeh').AsString) ;
          sharh_sanad:=' کارکرد بیمه '+name_bimeh(FieldByName('code_bimeh').AsString)+' '+ F_T;

          Sarjame_Sanad:=Sarjame_Sanad+Trunc(FieldByName('s_r_bimeh').AsCurrency);
         // Write_To_Tarce_File(CurrToStr(Sarjame_Sanad)+' + '+CurrToStr(FieldByName('s_r_bimeh').AsCurrency));


          if FieldByName('s_r_bimeh').AsCurrency > 0 then
          begin
          QY.Close;
          QY.SQL.Clear;
          QY.SQL.Add(' insert link_hesabdari (code_system,name_system,from_tarikh,to_tarikh,shomare_sanad,shobeh,');
          QY.SQL.Add(' radif,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20)values ');
          QY.SQL.Add(' (1'+','+''''+sharh_sanad+ ''''+','+''''+F_T+''''+','+''''+T_T+''''+',');
          QY.SQL.Add(''''+'shomare_sanad'+''''+','+''''+'shobeh'+''''+','+''''+'1'+''''+',' );
          QY.SQL.Add(''''+'f1'+''''+','+''''+'f2'+''''+','+''''+'f3'+''''+','+''''+'f4'+''''+','+''''+'f5'+''''+',');
          QY.SQL.Add(''''+'f6'+''''+','+''''+'f7'+''''+','+''''+'f8'+''''+','+''''+'f9'+''''+','+''''+'f10'+''''+',');
          QY.SQL.Add(''''+'f11'+''''+','+''''+'f12'+''''+','+''''+sharh_sanad+''''+','+''''+p_c_kol+''''+','+''''+p_c_goroh+''''+',');
          QY.SQL.Add(''''+p_c_moein+''''+','+''''+p_c_tafzil+''''+','+''''+FieldByName('code_bimeh').AsString+''''+','+''''+CurrToStr(Trunc(FieldByName('s_r_bimeh').AsCurrency))+''''+','+''''+'0'+''''+')');
          Write_To_Tarce_File(QY.SQL.Text);
          QY.ExecSQL;
          end;
          // ShowMessage(FieldByName('code_bimeh').AsString);
          Next;
     end;



4



      // for Variz_Be_Hesab_Banki
      Write_To_Tarce_File('  sarjame babat har bank   Variz_Be_Hesab_Banki ');

     Close;
     SQL.Clear;
     SQL.Add(' select code_bank,sum(mablagh) as s_mablagh ');
     SQL.Add(' from Variz_Be_Hesab_Banki  ');
     SQL.Add(' where tarikh between'''+F_T+''' and ''' +T_T+'''');
     SQL.Add(' group by code_bank order by code_bank ');
     Write_To_Tarce_File(SQL.Text);
     Open;

     while not Eof do
     begin

          p_c_kol :=code_hesab('code_kol','bank','code_bank',FieldByName('code_bank').AsString) ;
          p_c_goroh :=code_hesab('code_goroh','bank','code_bank',FieldByName('code_bank').AsString) ;
          p_c_moein :=code_hesab('code_moein','bank','code_bank',FieldByName('code_bank').AsString) ;
          p_c_tafzil :=code_hesab('code_tafzil','bank','code_bank',FieldByName('code_bank').AsString) ;

          sharh_sanad:='  کارکرد پوز یا واریز   '+name_hesab_banki(FieldByName('code_bank').AsString)+' '+  F_T;

          Sarjame_Sanad:=Sarjame_Sanad+Trunc(FieldByName('s_mablagh').AsCurrency);
          // Write_To_Tarce_File(CurrToStr(Sarjame_Sanad)+' + '+CurrToStr(FieldByName('s_mablagh').AsCurrency));


          if FieldByName('s_mablagh').AsCurrency > 0 then
          begin
          QY.Close;
          QY.SQL.Clear;
          QY.SQL.Add(' insert link_hesabdari (code_system,name_system,from_tarikh,to_tarikh,shomare_sanad,shobeh,');
          QY.SQL.Add(' radif,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20)values ');
          QY.SQL.Add(' (1'+','+''''+sharh_sanad+ ''''+','+''''+F_T+''''+','+''''+T_T+''''+',');
          QY.SQL.Add(''''+'shomare_sanad'+''''+','+''''+'shobeh'+''''+','+''''+'1'+''''+',' );
          QY.SQL.Add(''''+'f1'+''''+','+''''+'f2'+''''+','+''''+'f3'+''''+','+''''+'f4'+''''+','+''''+'f5'+''''+',');
          QY.SQL.Add(''''+'f6'+''''+','+''''+'f7'+''''+','+''''+'f8'+''''+','+''''+'f9'+''''+','+''''+'f10'+''''+',');
          QY.SQL.Add(''''+'f11'+''''+','+''''+'f12'+''''+','+''''+sharh_sanad+''''+','+''''+p_c_kol+''''+','+''''+p_c_goroh+''''+',');
          QY.SQL.Add(''''+p_c_moein+''''+','+''''+p_c_tafzil+''''+','+''''+FieldByName('code_bank').AsString+''''+','+''''+CurrToStr(Trunc(FieldByName('s_mablagh').AsCurrency))+''''+','+''''+'0'+''''+')');
          Write_To_Tarce_File(QY.SQL.Text);
          QY.ExecSQL;
          end;
          // ShowMessage(FieldByName('code_bank').AsString);
          Next;
     end;


5-



      // for pay   انواع پرداخت بجز پوز یا واریز
     Write_To_Tarce_File(' karkerd babat pardakht pay    ');


     Write_To_Tarce_File(' karkerd babat pardakht pay    ');

     Close;
     SQL.Clear;
     SQL.Add(' select code_pay,sum(r_bes) as s_r_bes ');
     SQL.Add(' from morajeat  ');
     SQL.Add(' where tarikh between'''+F_T+''' and ''' +T_T+'''');

     if code_not_mohasebe_st <> '' then
     SQL.Add(code_not_mohasebe_st);

     SQL.Add(' group by code_pay order by code_pay ');
     Write_To_Tarce_File(SQL.Text);
     Open;

     while not Eof do
     begin
        //  ShowMessage(FieldByName('code_pay').AsString);
          if FieldByName('code_pay').AsInteger <> 3 then
          begin

          p_c_kol :=code_hesab('code_kol','pay','code_pay',FieldByName('code_pay').AsString) ;
          p_c_goroh :=code_hesab('code_goroh','pay','code_pay',FieldByName('code_pay').AsString) ;
          p_c_moein :=code_hesab('code_moein','pay','code_pay',FieldByName('code_pay').AsString) ;
          p_c_tafzil :=code_hesab('code_tafzil','pay','code_pay',FieldByName('code_pay').AsString) ;

          sharh_sanad:=' کارکرد '+name_pay(FieldByName('code_pay').AsString)+' '+  F_T;

          Sarjame_Sanad:=Sarjame_Sanad+Trunc(FieldByName('s_r_bes').AsCurrency);
         // Write_To_Tarce_File(CurrToStr(Sarjame_Sanad)+' + '+CurrToStr(FieldByName('s_r_bes').AsCurrency));


          if FieldByName('s_r_bes').AsCurrency > 0 then
          begin
          QY.Close;
          QY.SQL.Clear;
          QY.SQL.Add(' insert link_hesabdari (code_system,name_system,from_tarikh,to_tarikh,shomare_sanad,shobeh,');
          QY.SQL.Add(' radif,f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20)values ');
          QY.SQL.Add(' (1'+','+''''+sharh_sanad+ ''''+','+''''+F_T+''''+','+''''+T_T+''''+',');
          QY.SQL.Add(''''+'shomare_sanad'+''''+','+''''+'shobeh'+''''+','+''''+'1'+''''+',' );
          QY.SQL.Add(''''+'f1'+''''+','+''''+'f2'+''''+','+''''+'f3'+''''+','+''''+'f4'+''''+','+''''+'f5'+''''+',');
          QY.SQL.Add(''''+'f6'+''''+','+''''+'f7'+''''+','+''''+'f8'+''''+','+''''+'f9'+''''+','+''''+'f10'+''''+',');
          QY.SQL.Add(''''+'f11'+''''+','+''''+'f12'+''''+','+''''+sharh_sanad+''''+','+''''+p_c_kol+''''+','+''''+p_c_goroh+''''+',');
          QY.SQL.Add(''''+p_c_moein+''''+','+''''+p_c_tafzil+''''+','+''''+FieldByName('code_pay').AsString+''''+','+''''+CurrToStr(trunc(FieldByName('s_r_bes').AsCurrency))+''''+','+''''+'0'+''''+')');
          Write_To_Tarce_File(QY.SQL.Text);
          QY.ExecSQL;
          end;
          // ShowMessage(FieldByName('code_pay').AsString);

          end;
          Next;
     end;



6 -








*/