if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Bolnich_Rasch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Bolnich_Rasch]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/****** Object:  Stored Procedure dbo.Bolnich_Rasch    Script Date: 22.06.2000 12:20:54 ******/
CREATE PROCEDURE Bolnich_Rasch (@Pcode int, @ln int, @dni_tp int, @dni_pp int, @SredDnTp money,
   @OgranTp money, @SredDnPp money,@OgranPp money, @Vid int, @curdate datetime, @Stazh int, 
   @d_nach datetime, @d_okon datetime) AS
/* @Pcode - номер записи в таблице z_boln,
    @ln - личный номер работника,
    @dni_tp - оплачиваемые дни больничного текущего периода,
    @dni_pp - оплачиваемые дни больничного прошлого периода,
    @SredDnTp - среднедневной фактический заработок текущего периода,
    @SredDnPp - среднедневной фактический заработок прошлого периода,
    @OgranTp - ограничение 85-кратным МРОТ текущего периода,
    @OgranPp - ограничение 85-кратным МРОТ прошлого периода,
    @vid - вид расчета больничного листа,
    @curdate - текущий месяц,
    @Stazh - непрерывный стаж работы,
    @d_nach - дата начала больничного,
    @d_okon - дата окончания больничного,
    @dt - пятый день при расчете 2-го вида больничного, 
    @vc - переменная,
    @Dopm - последний день месяца,
    @mo - признак одинокого родителя
    @dd - дата до которой плитится больничный по стажу для одинак родит.

*/
declare @RK money, @dt datetime
declare @vc int, @SredDn money, @srk money, @Summa money
declare @MPOT money, @MPOTPp money, @Days int, @Proc money, @Dopm datetime
declare @mo int, @dd datetime, @d int, @dn int
declare @Procent money
select @Procent=(case when @vid in (3, 5) then 100
     		           when @Stazh < 50000 then 60
		           when @Stazh >= 50000 and @Stazh < 80000 then 80
		           when @Stazh >= 80000 then 100 end)
select @RK=0.15
select @MPOT=convert(money,Znach) from ZarNastr where godmes=@curdate and Kod='MROT'
/*select @MPOT=132*/
select @SredDnTp=round(@SredDnTp,2)
select @SredDnPp=round(@SredDnPp,2)
select @Dopm=dateadd(day,-1,@curdate)
select @MPOTPp=convert(money,Znach) from ZarNastr where godmes=dateadd(month,-1,@curdate) and Kod='MROT'
/*select @MPOTPp=132 */
--select @OgranPp=round(@MPOTPp*85*1.15/round(g.ChMonth/g.ChDay,0),2)
select @OgranPp=round(14280 * 1.15 / round(g.ChMonth/g.ChDay,0),2)
             from zarr z, z_graphic g
             where z.godmes=dateadd(month,-1,@curdate) and ln=@ln
             and g.nomer=z.GRAPHIC and g.godmes=z.godmes
--select @OgranTp=round(@MPOT*85*1.15/round(g.ChMonth/g.ChDay,0),2)
select @OgranTp=round(14280*1.15/round(g.ChMonth/g.ChDay,0),2)
             from zarr z, z_graphic g
             where z.godmes=@curdate and ln=@ln
             and g.nomer=z.GRAPHIC and g.godmes=z.godmes

-- одинокий родитель или нет
select 	@mo = case when (select isnull(u.code,0) 
			 from 	kadry k (nolock) left outer join 
				unispr u (nolock) on k.kategoria = u.vcode
			 where 	k.vcode = @ln )= 2 then 1 else 0 end 

select 	@dd = case when @mo = 1 
		   then dateadd(day,9,@d_nach) 
		   else dateadd(day,6,@d_nach) end
select 	@d = case when @mo = 1 then 9 else 6 end	

execute @dn=Bolnich_Dni @d_nach, @dd


--- Расчет больничного листа по уходу за детьми
if @Vid=2 and (@dni_tp+@dni_pp)>@dn
begin

--    execute Bolnich_Period @d_nach,5, @dt out
      select @dt = dateadd(day,@d,@d_nach)
--- Первые 5 дней
    if @d_nach < @curdate and @dt < @curdate
    begin
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnPp*@Procent/100,2) < round(@OgranPp,2) 
			       then round(@SredDnPp*@Procent/100,2) else round(@OgranPp,2) end)
        select @Summa=round(@SredDn*@dn,2)
        select @srk=round(@SredDn*@dn*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Procent,@dn,@d_nach,@dt,@SredDn,@Summa,@srk,1,@OgranPp)    
    end
    if @d_nach >= @curdate and @dt >= @curdate
    begin
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnTp*@Procent/100,2) < round(@OgranTp,2)
			       then round(@SredDnTp*@Procent/100,2) else round(@OgranTp,2) end)
        select @Summa=round(@SredDn*@dn,2)
        select @srk=round(@SredDn*@dn*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Procent,@dn,@d_nach,@dt,@SredDn,@Summa,@srk,0,@OgranTp)    
    end
    if @d_nach < @curdate and @dt >= @curdate
    begin
        EXECUTE @Days= Bolnich_dni @d_nach, @Dopm
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnPp*@Procent/100,2) < round(@OgranPp,2)
			       then round(@SredDnPp*@Procent/100,2) else round(@OgranPp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Procent,@Days,@d_nach,dateadd(day,-1,@curdate),@SredDn,@Summa,@srk,1,@OgranPp)    
        EXECUTE @Days= Bolnich_dni @curdate, @dt
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnTp*@Procent/100,2) < round(@OgranTp,2)
			       then round(@SredDnTp*@Procent/100,2) else round(@OgranTp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Procent,@Days,@curdate,@dt,@SredDn,@Summa,@srk,0,@OgranTp)    
    end
--- Оставшиеся дни
    select @Proc=50
    select @dt=dateadd(day,1,@dt)
    if @dt < @curdate and @d_okon < @curdate
    begin
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @Days=@dni_tp+@dni_pp-@dn
        select @SredDn=(case when round(@SredDnPp*@Proc/100,2) < round(@OgranPp,2)
			       then round(@SredDnPp*@Proc/100,2) else round(@OgranPp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Proc,@Days,@dt,@d_okon,@SredDn,@Summa,@srk,1,@OgranPp)    
    end
    if @dt >= @curdate and @d_okon >= @curdate
    begin
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @Days=@dni_tp+@dni_pp-@dn
        select @SredDn=(case when round(@SredDnTp*@Proc/100,2) < round(@OgranTp,2)
			       then round(@SredDnTp*@Proc/100,2) else round(@OgranTp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Proc,@Days,@dt,@d_okon,@SredDn,@Summa,@srk,0,@OgranTp)    
    end
    if @dt < @curdate and @d_okon >= @curdate
    begin
        EXECUTE @Days= Bolnich_dni @dt, @Dopm
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnPp*@Proc/100,2) < round(@OgranPp,2)
			       then round(@SredDnPp*@Proc/100,2) else round(@OgranPp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Proc,@Days,@dt,dateadd(day,-1,@curdate),@SredDn,@Summa,@srk,1,@OgranPp)    
        EXECUTE @Days= Bolnich_dni @curdate, @d_okon
        EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
        select @SredDn=(case when round(@SredDnTp*@Proc/100,2) < round(@OgranTp,2)
			       then round(@SredDnTp*@Proc/100,2) else round(@OgranTp,2) end)
        select @Summa=round(@SredDn*@Days,2)
        select @srk=round(@SredDn*@Days*@RK/(1+@RK),2)
        INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
           VALUES (@vc,@Pcode,@Proc,@Days,@curdate,@d_okon,@SredDn,@Summa,@srk,0,@OgranTp)    
    end
end
else
begin
   if @dni_tp > 0
   begin
      EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
      select @SredDn=(case when round(@SredDnTp*@Procent/100,2) < round(@OgranTp,2)
			     then round(@SredDnTp*@Procent/100,2) else round(@OgranTp,2) end)
      select @Summa=round(@SredDn*@dni_tp,2)
      select @srk=round(@SredDn*@dni_tp*@RK/(1+@RK),2)
      if @d_nach  > @curdate
         select @dt=@d_nach
     else
         select @dt=@curdate
      INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
        VALUES (@vc,@Pcode,@Procent,@dni_tp,@dt,@d_okon,@SredDn,@Summa,@srk,0,@OgranTp)
   end
   if @dni_pp > 0
   begin
      EXECUTE @vc= sp_autocod 'BOLNDOPCODE'
      select @SredDn=(case when round(@SredDnPp*@Procent/100,2) < round(@OgranPp,2)
			     then round(@SredDnPp*@Procent/100,2) else round(@OgranPp,2) end)
      select @Summa=round(@SredDn*@dni_pp,2)
      select @srk=round(@SredDn*@dni_pp*@RK/(1+@RK),2)
      if @d_okon  > @curdate
         select @dt=dateadd(day,-1,@curdate)
     else
         select @dt=@d_okon
      INSERT INTO z_boln_dop (VCode,PCode,Procent,Dni,D_Nach,D_Okon,Sum_Dn,Summa,Rk,Prosh,Ogran) 
        VALUES (@vc,@Pcode,@Procent,@dni_pp,@d_nach,@dt,@SredDn,@Summa,@srk,1,@OgranPp)
   end
end
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO