Delimiter $

drop procedure if exists sp_zpt_rn_sd_yet_10;
CREATE PROCEDURE sp_zpt_rn_sd_yet_10(param_year int, param_month int, param_d datetime)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_dnpl double (19,10);
declare var_kou int;
declare var_tn int;
declare var_npp int;
declare var_dn double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
DECLARE done INT DEFAULT 0;
declare var_minZPT double (19,10);

declare c_tn10 cursor
for select zpt_iz.tn,DATEDIFF(DATE_ADD(DATE_ADD(zpt_iz.dr,INTERVAL 3 year),INTERVAL 0 DAY),param_d)+1 as dn,
tn.nv,zpt_bux.npp from zpt_iz
 left join tn on zpt_iz.tn=tn.tn
 left join zpt_bux on zpt_bux.tn=zpt_iz.tn and zpt_bux.osn=1
 where zpt_iz.b3=1 and (du is null or (du is not null and du>=param_d)) and
 (DATE_ADD(DATE_ADD(zpt_iz.dr,INTERVAL 3 year),INTERVAL 0 DAY)>param_d);
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

select d1 from otp where g=param_year and ms=param_month into var_dpv1;
select 100 into var_minZPT;

/*пособие ребенку до 3 лет*/
select 18 into var_kou;
select datediff(date_add(param_d,INTERVAL 1 MONTH),param_d) into var_dnpl;
open c_tn10;
 REPEAT
  fetch c_tn10 into var_tn,var_dn,var_nv,var_npp;
  IF NOT done THEN
   select count(npp) from zpt_bux where tn=var_tn and d2 is null into var_count;
   select max(d2) from zpt_bux where tn=var_tn and d2 is not null into var_d2;
   if (var_count=0 or var_count is null) and (var_d2 is not null and var_d2<var_dpv1) then
    if var_dn>var_dnpl then
	 select var_dnpl into var_dn;
	end if;
    select round(.5*var_minZPT*var_dn/var_dnpl,2) into var_s;
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn10;
end$

drop procedure if exists sp_zpt_rn_sd_yet_11;

CREATE PROCEDURE sp_zpt_rn_sd_yet_11(param_year int, param_month int, param_tn int, param_ProjectType int, param_kou int, param_pr double (19,10))
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_dnpl double (19,10);
declare var_npp int;
declare var_dn double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
declare var_minZPT double (19,10);
declare var_yet double (19,10);
declare var_yet1 double (19,10) default 999999999;
declare var_yet2 double (19,10) default 0;
declare var_yet3 double (19,10);
declare var_zpt double (19,10);
declare var_nv1 int;
declare var_nv2 int;
DECLARE doneNV INT DEFAULT 0;

declare c_nv cursor
 for select distinct nv from zpt_NUz where g=param_year and ms=param_month and tn=param_tn and (nv<>3 or param_ProjectType<>3 or param_kou<>206);

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneNV = 1;

select sum(s) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=param_kou and zpt_NUz.tn=param_tn and zpt_NUz.kou<=200 and (zpt_NUz.nv<>3 or param_ProjectType<>3 or param_kou<>206)
 union
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=param_kou and zpt_NUz.tn=param_tn and zpt_NUz.kou>200 and (zpt_NUz.nv<>3 or param_ProjectType<>3 or param_kou<>206)
) q into var_zpt;
select round(var_zpt*param_pr/100,2) into var_yet;

SET doneNV = 0;
open c_nv;
REPEAT
 fetch c_nv into var_nv;
 IF NOT doneNV THEN
 select sum(if(isnull(s),0,s)) from (
  select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=param_kou and zpt_NUz.tn=param_tn and zpt_NUz.nv=var_nv and zpt_NUz.kou<=200
  union
  select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=param_kou and zpt_NUz.tn=param_tn and zpt_NUz.nv=var_nv and zpt_NUz.kou>200
  ) q into var_zpt;
  if var_zpt<var_yet1 then
      select var_nv into var_nv1;
      select var_zpt into var_yet1;
     end if;
     if var_zpt>var_yet2 then
      select var_nv into var_nv2;
      select var_zpt into var_yet2;
     end if;
     if var_zpt>0 then
      select round(var_zpt*param_pr/100,2) into var_s;
      select var_yet-var_s into var_yet;
      if var_s>0 then
       select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv and kou=param_kou and tn=param_tn into var_count;
       if var_count>0 then
	    update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=param_kou and tn=param_tn;
       else
	    insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv,param_kou,param_tn,var_s);
	   end if; /*if var_count>0 then*/
      end if; /*if var_s>0 then*/
     end if; /*if var_zpt>0 then*/
    end if; /*IF NOT doneNV THEN*/
   UNTIL doneNV END REPEAT;
   CLOSE c_nv;
   
if var_yet<0 then
 update zpt_NUz set s=s+var_yet where g=param_year and ms=param_month and nv=var_nv2 and kou=param_kou and tn=param_tn;
else
 if var_yet>0 then
  update zpt_NUz set s=s+var_yet where g=param_year and ms=param_month and nv=var_nv1 and kou=param_kou and tn=param_tn;
 end if; /*if var_yet>0 then*/
end if; /*if var_yet<0 then*/

end$

drop procedure if exists sp_zpt_rn_sd_yet_12;
CREATE PROCEDURE sp_zpt_rn_sd_yet_12(param_year int, param_month int, param_tn int, param_prd int, param_kat int, param_r_sov double (19,10), param_r_lg double (19,10), param_r_ud double (19,10))
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_dnpl double (19,10);
declare var_kou int default 211;
declare var_npp int;
declare var_dn double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
declare var_minZPT double (19,10);
declare var_yet double (19,10);
declare var_yet1 double (19,10) default 999999999;
declare var_yet2 double (19,10) default 0;
declare var_yet3 double (19,10);
declare var_zpt double (19,10);
declare var_nv1 int;
declare var_nv2 int;
declare var_obl double (19,10);
declare var_lg_mes double (19,10);
declare var_pu_mes double (19,10);
declare var_pu_god double (19,10);
declare var_ru_god double (19,10);
declare var_sovMS double (19,10);
declare var_kof double (19,10) default 400;
declare var_sov double (19,10);
declare var_ud_this_month double (19,10);

DECLARE doneNV INT DEFAULT 0;

declare c_nv cursor
 for select distinct nv from zpt_NUz where g=param_year and ms=param_month and tn=param_tn;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneNV = 1;

select sum(if(isnull(s),0,s)) from ( 
 select sum(s_) as s from 
 ( 
  select zpt_nuz.s,vych_s,round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2) as s_ from 
  ( 
   select spr_tvx.id as idd from spr_vou 
   inner join spr_tvx on spr_tvx.kou=spr_vou.id 
   where (alg=30 or alg=31 or alg=32) and spr_tvx.id<=200 
  ) q_ 
  left join spr_vou on spr_vou.id=idd 
  left join zpt_nuz on zpt_nuz.kou=idd 
  where zpt_nuz.g=param_year and zpt_nuz.ms<=param_month and zpt_nuz.tn=param_tn
  group by ms,nv,zpt_nuz.kou 
  having((round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2)>0 and vych_s>0) or vych_s=0 or vych_s is null) 
  union all 
  select zpt_nuz.s,vych_s,-round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2) as s_ from 
  ( 
   select spr_tvx.id as idd from spr_vou 
   inner join spr_tvx on spr_tvx.kou=spr_vou.id 
   where (alg=30 or alg=31 or alg=32) and spr_tvx.id>200 
  ) q2_ 
  left join spr_vou on spr_vou.id=idd 
  left join zpt_nuz on zpt_nuz.kou=idd 
  where zpt_nuz.g=param_year and zpt_nuz.ms<=param_month and zpt_nuz.tn=param_tn 
  group by ms,nv,zpt_nuz.kou 
  having((round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2)>0 and vych_s>0) or vych_s=0 or vych_s is null) 
  ) cc 
) query into var_sov;

select var_sov+param_r_sov into var_sov;


/*
if param_tn=41 then
 update constants set name=concat(var_sov,' ',param_r_sov) where Identity='tarific_chlens';
 end if;
 */







select sum(if(isnull(s),0,s)) from ( 
 select sum(s_) as s from 
 ( 
  select zpt_nuz.s,vych_s,round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2) as s_ from 
  ( 
   select spr_tvx.id as idd from spr_vou 
   inner join spr_tvx on spr_tvx.kou=spr_vou.id 
   where (alg=30 or alg=31 or alg=32) and spr_tvx.id<=200 
  ) q_ 
  left join spr_vou on spr_vou.id=idd 
  left join zpt_nuz on zpt_nuz.kou=idd 
  where zpt_nuz.g=param_year and zpt_nuz.ms=param_month and zpt_nuz.tn=param_tn
  group by ms,nv,zpt_nuz.kou 
  having((round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2)>0 and vych_s>0) or vych_s=0 or vych_s is null) 
  union all 
  select zpt_nuz.s,vych_s,-round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2) as s_ from 
  ( 
   select spr_tvx.id as idd from spr_vou 
   inner join spr_tvx on spr_tvx.kou=spr_vou.id 
   where (alg=30 or alg=31 or alg=32) and spr_tvx.id>200 
  ) q2_ 
  left join spr_vou on spr_vou.id=idd 
  left join zpt_nuz on zpt_nuz.kou=idd 
  where zpt_nuz.g=param_year and zpt_nuz.ms=param_month and zpt_nuz.tn=param_tn 
  group by ms,nv,zpt_nuz.kou 
  having((round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2)>0 and vych_s>0) or vych_s=0 or vych_s is null) 
  ) cc 
) query into var_sovMS;

if var_sovMS<=0 then
 delete from zpt_m_ where g=param_year and ms=param_month and tn=param_tn;
end if;

update zpt_m_ 
left join spr_zpt_lgoty on g=param_year and ms=param_month and tn=param_tn and id=lg
set zpt_m_.s=0
where g=param_year and ms=param_month and tn=param_tn and var_sov>ogr and ogr>0;

delete from zpt_m_ where s=0 and g=param_year and ms=param_month and tn=param_tn;

select sum(s) from zpt_m_ where g=param_year and ms<=param_month and tn=param_tn into var_lg_mes;
if var_lg_mes is null then 
 select 0 into var_lg_mes;
end if;

select round(var_sov-(param_r_lg+var_lg_mes),2) into var_obl;

if var_obl<0 then
  select 0 into var_obl;
end if;

select round(var_obl*.13,0) into var_pu_god;

 update zpt_M set prd=param_prd,kat=param_kat
 where g=param_year and ms=param_month and tn=param_tn;

 select round(param_r_ud+sum(s),0) from
  (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_vou on spr_vou.id=zpt_NUz.kou where g=param_year and ms<param_month and tn=param_tn and (alg=30 or alg=31 or alg=32)
   union
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where g=param_year and ms<param_month and tn=param_tn and kou=11
   union
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_vou on spr_vou.id=zpt_NUz.kou where g=param_year and ms=param_month and tn=param_tn and (alg=31 or alg=32)
  ) q into var_ru_god;
  
 select sum(s) from
  (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_vou on spr_vou.id=zpt_NUz.kou where g=param_year and ms=param_month and tn=param_tn and (alg=30 or alg=31 or alg=32)
   union
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where g=param_year and ms=param_month and tn=param_tn and kou=11
  ) q into var_ud_this_month;






 select var_pu_god-var_ru_god into var_pu_mes;
 
 
if var_pu_mes-var_ud_this_month<0 then
  insert into zpt_vozvr (g,ms,tn,s) values (param_year,param_month,param_tn,-(var_pu_mes-var_ud_this_month));
  select 0 into var_pu_mes;
 else
  if var_sovMS-var_pu_mes<0 then
   insert into zpt_vozvrz (g,ms,tn,s) values (param_year,param_month,param_tn,var_pu_mes-var_sovMS);
   select var_sovMS into var_pu_mes;
  end if;
  
  select var_pu_mes into var_yet;
  open c_nv;
  REPEAT
  fetch c_nv into var_nv;
  IF NOT doneNV THEN
   select sum(s) from (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=param_tn and zpt_NUz.nv=var_nv and zpt_NUz.kou<=200
   union
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=param_tn and zpt_NUz.nv=var_nv and zpt_NUz.kou>200
   ) q into var_zpt;
   if var_zpt<var_yet1 then
    select var_nv into var_nv1;
    select var_zpt into var_yet1;
   end if;
   if var_zpt>var_yet2 then
    select var_nv into var_nv2;
    select var_zpt into var_yet2;
   end if;
   if var_zpt>0 then
    select round(var_pu_mes*var_zpt/var_sovMS,0) into var_s;
    select var_yet-var_s into var_yet;
    if var_s>0 then
     select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=param_tn into var_count;
     if var_count>0 then
	  update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=param_tn;
     else
	  insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv,var_kou,param_tn,var_s);
	 end if;/*if var_count>0 then*/
    end if; /*if var_s>0 then*/
   end if; /*if var_zpt>0 then*/
  end if; /*IF NOT doneNV THEN*/
  UNTIL doneNV END REPEAT;
  CLOSE c_nv;
 end if;

if var_yet<0 then
 update zpt_NUz set s=s+var_yet where g=param_year and ms=param_month and nv=var_nv2 and kou=var_kou and tn=param_tn;
else
 if var_yet>0 then
  update zpt_NUz set s=s+var_yet where g=param_year and ms=param_month and nv=var_nv1 and kou=var_kou and tn=param_tn;
 end if;
end if;
end$

drop procedure if exists sp_zpt_rn_sd_yet_13;

CREATE PROCEDURE sp_zpt_rn_sd_yet_13(param_year int, param_month int, param_nv int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_dnpl double (19,10);
declare var_kou int default 211;
declare var_npp int;
declare var_dn double (19,10);
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
declare var_minZPT double (19,10);
declare var_yet double (19,10);
declare var_yet1 double (19,10) default 999999999;
declare var_yet2 double (19,10) default 0;
declare var_yet3 double (19,10);
declare var_zpt double (19,10);
declare var_tn int;
declare var_pr double (19,10);
declare var_sv double (19,10);
declare var_ov double (19,10);

DECLARE done INT DEFAULT 0;

declare c_tn cursor
 for select tn,npp,kou,pr,if(isnull(sv),0,sv) from zpt_isp where pu=1 and pr>0 and one<>1;


DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
open c_tn;
repeat
 fetch c_tn into var_tn,var_npp,var_kou,var_pr,var_sv;
 IF NOT done THEN
  select sum(if(isnull(s),0,s)) from (
  select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=var_tn and zpt_NUz.nv=param_nv and zpt_NUz.kou<=200
  union
  select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=var_tn and zpt_NUz.nv=param_nv and zpt_NUz.kou>200
  ) q into var_zpt;
  select round(var_zpt*var_pr/100,2) into var_s;
  if var_sv>0 then
   select var_sv-if(isnull(sum(s)),0,sum(s)) from zpt_isp1 where tn=var_tn and npp=var_npp into var_ov;
   if var_ov>0 then
    select var_ov-var_s into var_zpt;
    if var_zpt<0 then
	 select var_s+var_zpt into var_s;
	end if; /*if var_zpt<0 then*/
   else 
    select 0 into var_s;
   end if; /*if var_ov>0 then*/
  end if; /*if var_sv>0 then*/
  if var_s>0 then
   select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=param_nv and kou=var_kou and tn=var_tn into var_count;
   if var_count>0 then
    update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=param_nv and kou=var_kou and tn=var_tn;
   else 
    insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,param_nv,var_kou,var_tn,var_s);
   end if;
   select count(*) from zpt_isp1 where g=param_year and ms=param_month and tn=var_tn and npp=var_npp into var_count;
   if var_count>0 then
    update zpt_isp1 set s=s+var_s where g=param_year and ms=param_month and tn=var_tn and npp=var_npp;
   else
    insert into zpt_isp1 (g,ms,tn,npp,s) values (param_year,param_month,var_tn,var_npp,var_s);
   end if;
  end if;
end if;
UNTIL done END REPEAT;
CLOSE c_tn;
end$

drop procedure if exists sp_zpt_minusy;

CREATE PROCEDURE sp_zpt_minusy(param_year int,param_month int,param_tn int,param_nv int,param_s double (19,10),param_kou int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN

declare var_count int;
declare var_nv1 int;
declare var_s1 double (19,10);
declare var_sov double (19,10);
declare var_yet1 double (19,10);
declare var_min double (19,10);
DECLARE done INT DEFAULT 0;

declare c_tn1 cursor
  for select nv,sum(s) from (
  select nv,if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and tn=param_tn and zpt_NUz.kou<=200 group by nv
  union
  select nv,-if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and tn=param_tn and zpt_NUz.kou>200 group by nv
  ) q  group by nv having sum(s)>0;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

/*ищем минусы работников у которых есть минусы по к-л ведомости*/
select s from zpt_NUz where g=param_year and ms=param_month and tn=param_tn and kou=param_kou and nv=param_nv into var_yet1;
if var_yet1>0 then /*есть начисления по этому виду оплат, попробуем их убавить*/
 /*ищем плюсы*/
 open c_tn1;
 repeat
  fetch c_tn1 into var_nv1,var_s1;
  IF NOT done THEN
   if var_yet1<-param_s then 
    select var_yet1 into var_min;
   else
    select -param_s into var_min;
   end if;
   if var_s1-var_min>=0 then
    select var_min into var_sov;
   else
    select var_s1 into var_sov;
   end if;
   select var_yet1-var_sov into var_yet1;
   update zpt_NUz set s=s-var_sov where g=param_year and ms=param_month and nv=param_nv and kou=param_kou and tn=param_tn;
   select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv1 and kou=param_kou and tn=param_tn into var_count;
   if var_count>0 then
    update zpt_NUz set s=s+var_sov where g=param_year and ms=param_month and nv=var_nv1 and kou=param_kou and tn=param_tn;
   else
    insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv1,param_kou,param_tn,var_sov);
   end if;
   if var_yet1<=0 then
    set done=1;
   end if;
  end if; /*IF NOT done THEN*/
 UNTIL done END REPEAT;
 CLOSE c_tn1;
end if;
end$

drop procedure if exists sp_zpt_rn_sd_yet_14;
CREATE PROCEDURE sp_zpt_rn_sd_yet_14(param_year int, param_month int, param_tn int, param_id int, param_pr double (19,10), var_points int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_pu_mes double (19,10);
declare var_kou int;
declare var_npp int;
declare var_yet double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
declare var_sov double (19,10);
declare var_sov_ms double (19,10);
declare var_sov_ms_ double (19,10);
declare var_zpt double (19,10);
declare var_nv1 int;
declare var_nv2 int;
declare var_id int DEFAULT 0;
DECLARE doneNV INT DEFAULT 0;
declare var_sss_minus double (19,10) default 0;
declare var_pr double (19,10);
declare var_s1 double (19,10);
declare var_s2 double (19,10);
declare var_tmp_ms double (19,10);
declare var_s_ms double (19,10);
declare tmp_ double (19,10);

declare c_nv cursor
 for SELECT distinct nv from zpt_NUz where g=param_year and ms=param_month and tn=param_tn group by nv;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneNV = 1;

select param_pr into var_pr;

if param_id=1 then
 select 126 into var_id;
end if;
if param_id=3 then
 select 128 into var_id;
end if;
if param_id=5 then
 select 122 into var_id;
end if;
if param_id=7 then
 select 127 into var_id;
end if;

/*начислено за текущий месяц по данному параметру param_id*/
/* необходимо для распределения совокупного */
select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200
) query into var_sov_ms_;

/*начислено за текущий месяц по всем статьям*/
select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou>200
) query into var_sov_ms;
/*всего начислено за год*/
select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou>200
) query into var_sov;

select var_sov_ms into var_s1;
/*
	var_s2 - на сколько превышает 280000 (600000)
*/

if var_sov>600000 then
 select 600000 into var_sss_minus;
 if var_id=126 then
  select 0 into var_pr;
 end if;
 if var_id=128 then
  select 0 into var_pr;
 end if;
 if var_id=122 then
  select 0 into var_pr;
 end if;
 if var_id=127 then
  select 0 into var_pr;
 end if;


 select var_sss_minus - (var_sov - var_sov_ms) into var_s1;
 select var_sov_ms - var_s1 into var_s2;
 
 
 

 /*подлежит удержанию за год*/
 select round(var_s2*var_pr/100,var_points) into var_zpt;
 /*подлежит удержанию за месяц*/
 select var_zpt-if(isnull(sum(s1)),0,sum(s1)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=2 into var_pu_mes;

 if var_pu_mes<>0 or 1=1 then
  select var_pu_mes into var_yet;
 
  select sum(s2) from zpt_per where tn=param_tn and g=param_year and ms<param_month and npp=param_id and sov_tp=2 into tmp_;
  select var_s2-if(isnull(tmp_),0,tmp_) into var_s2; 
  select var_s2 into var_tmp_ms;

 /*распределяем по ведомостям пропорционально начисленному за месяц*/
 open c_nv;
 repeat
  fetch c_nv into var_nv;
  IF NOT doneNV THEN
   select if(isnull(sum(s)),0,sum(s)) from (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
   union 
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
   ) query into var_zpt;
   select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;
   select round(var_s2*var_zpt/var_sov_ms_, 2) into var_s_ms;
   if var_s<>0 or 1=1 then
    insert into zpt_per (g,ms,npp,tn,nv,s1,s2,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s,var_s_ms,2);
    select var_yet-var_s into var_yet;
    select var_tmp_ms-var_s_ms into var_tmp_ms;
    select var_nv into var_nv1;
   end if;
  end if;
 UNTIL doneNV END REPEAT;
 CLOSE c_nv;
SET doneNV = 0;
 if var_yet<>0 then
  update zpt_per set s1=s1+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=2;
 end if;

 end if; /*if var_pu_mes<>0 then*/

 select param_pr into var_pr;
 select var_sss_minus into var_sov;
end if; /*600000*/

if var_sov>=280001 and var_sov<=600000 then
 select 280000 into var_sss_minus;
 if var_id=126 then
  select 0.6 into var_pr;
 end if;
 if var_id=128 then
  select 1 into var_pr;
 end if;
 if var_id=122 then
  select 2.4 into var_pr;
 end if;
 if var_id=127 then
  select 0.5 into var_pr;
 end if;


 select var_sss_minus - (var_sov - var_sov_ms) into var_s1;
 select var_sov_ms - var_s1 into var_s2;

 /*подлежит удержанию за год*/
 select round(var_s2*var_pr/100,var_points) into var_zpt;
 /*подлежит удержанию за месяц*/
 select var_zpt-if(isnull(sum(s1)),0,sum(s1)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=1 into var_pu_mes;

 if var_pu_mes<>0 or 1=1 then
  select var_pu_mes into var_yet;
 
  select sum(s2) from zpt_per where tn=param_tn and g=param_year and ms<param_month and npp=param_id and sov_tp=1 into tmp_;
  select var_s2-if(isnull(tmp_),0,tmp_) into var_s2; 
  select var_s2 into var_tmp_ms;

 /*распределяем по ведомостям пропорционально начисленному за месяц*/
 open c_nv;
 repeat
  fetch c_nv into var_nv;
  IF NOT doneNV THEN
   select if(isnull(sum(s)),0,sum(s)) from (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
   union 
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
   ) query into var_zpt;
   select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;
   select round(var_s2*var_zpt/var_sov_ms_, 2) into var_s_ms;
   if var_s<>0 or 1=1 then
    insert into zpt_per (g,ms,npp,tn,nv,s1,s2,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s,var_s_ms,1);
    select var_yet-var_s into var_yet;
    select var_tmp_ms-var_s_ms into var_tmp_ms;
    select var_nv into var_nv1;
   end if;
  end if;
 UNTIL doneNV END REPEAT;
 CLOSE c_nv;
SET doneNV = 0;
 if var_yet<>0 then
  update zpt_per set s1=s1+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=1;
 end if;

 end if; /*if var_pu_mes<>0 then*/

 select param_pr into var_pr;
 select var_sss_minus into var_sov;
end if; /*280000

/*подлежит удержанию за год*/
select round(var_sov*var_pr/100,var_points) into var_zpt;

/*подлежит удержанию за месяц*/
select var_zpt-if(isnull(sum(s1)),0,sum(s1)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=0 into var_pu_mes;

 if var_pu_mes<>0 then
  select var_pu_mes into var_yet;
  select var_s1 into var_tmp_ms;
 

/*распределяем по ведомостям пропорционально начисленному за месяц*/
 open c_nv;
 repeat
  fetch c_nv into var_nv;
  IF NOT doneNV THEN
   select if(isnull(sum(s)),0,sum(s)) from (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
   union 
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
   ) query into var_zpt;
   select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;
   select round(var_s1*var_zpt/var_sov_ms_,2) into var_s_ms;
   if var_s<>0 then
    insert into zpt_per (g,ms,npp,tn,nv,s1,s2,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s,var_s_ms,0);
    select var_yet-var_s into var_yet;
    select var_tmp_ms-var_s_ms into var_tmp_ms;
    select var_nv into var_nv1;
   end if;
  end if;
 UNTIL doneNV END REPEAT;
 CLOSE c_nv;

 if var_yet<>0 then
  update zpt_per set s1=s1+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=0;
 end if;
 end if; /*if var_pu_mes<>0 then*/
end$

drop procedure if exists sp_zpt_rn_sd_yet_14_226;
CREATE PROCEDURE sp_zpt_rn_sd_yet_14_226(param_year int, param_month int, param_tn int, param_id int, param_pr double (19,10), var_points int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_pu_mes double (19,10);
declare var_kou int;
declare var_npp int;
declare var_yet double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_dpv1 datetime;
declare var_sov double (19,10);
declare var_zpt double (19,10);
declare var_nv1 int;
declare var_nv2 int;
DECLARE doneNV INT DEFAULT 0;
declare var_sss_minus double (19,10) default 0;
declare var_sov_tp int DEFAULT 0;
declare var_pr double (19,10);
declare var_id int DEFAULT 0;
declare var_tmp_ms double (19,10);

declare c_nv cursor
 for SELECT distinct nv from zpt_NUz where g=param_year and ms=param_month and tn=param_tn group by nv;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneNV = 1;
select param_pr into var_pr;


if param_id=126 then
 select 1 into var_id;
end if;
if param_id=128 then
 select 3 into var_id;
end if;
if param_id=122 then
 select 5 into var_id;
end if;
if param_id=127 then
 select 7 into var_id;
end if;

/*всего начислено за год*/
select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou>200
) query into var_sov;


if var_sov>=280001 and var_sov<=600000 then
 select 280000 into var_sss_minus;
 select 1 into var_sov_tp;
 if param_id=126 then
  select 0.6 into var_pr;
 end if;
 if param_id=128 then
  select 1 into var_pr;
 end if;
 if param_id=122 then
  select 2.4 into var_pr;
 end if;
 if param_id=127 then
  select 0.5 into var_pr;
 end if;
end if;

if var_sov>600000 then
 select 600000 into var_sss_minus;
 select 2 into var_sov_tp;
 if param_id=126 then
  select 0 into var_pr;
 end if;
 if param_id=128 then
  select 0 into var_pr;
 end if;
 if param_id=122 then
  select 0 into var_pr;
 end if;
 if param_id=127 then
  select 0 into var_pr;
 end if;
end if;

select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200
) query into var_sov;
select round(var_sov*var_pr/100,var_points) into var_zpt;

select var_zpt into var_pu_mes;

if var_pu_mes<>0 then
 select if(isnull(sum(s)),0,sum(s)) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200
 ) query into var_sov;

 select var_pu_mes into var_yet;
  select var_sov into var_tmp_ms;

 open c_nv;
 repeat
  fetch c_nv into var_nv;
  IF NOT doneNV THEN
   select if(isnull(sum(s)),0,sum(s)) from (
   select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
   union 
   select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
   ) query into var_zpt;
   select round(var_pu_mes*var_zpt/var_sov,var_points) into var_s;
   if var_s<>0 then
    insert into zpt_per (g,ms,npp,tn,nv,s1,s2,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s,var_zpt,var_sov_tp);
    select var_yet-var_s into var_yet;
    select var_tmp_ms-var_zpt into var_tmp_ms;
    select var_nv into var_nv1;
   end if;
  end if;
 UNTIL doneNV END REPEAT;
 CLOSE c_nv;

 if var_yet<>0 then
  update zpt_per set s1=s1+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=var_sov_tp;
 end if;
 if var_tmp_ms<>0 then
  update zpt_per set s2=s2+var_tmp_ms where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=var_sov_tp;
 end if;
end if;
end$


drop procedure if exists sp_zpt_rn_sd_yet_15;
CREATE PROCEDURE sp_zpt_rn_sd_yet_15(param_year int, param_month int, param_tn int, param_id int, param_gr int, var_points int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_count int;
declare var_pu_mes double (19,10);
declare var_kou int;
declare var_npp int;
declare var_yet double (19,10);
declare var_nv int;
declare var_D2 datetime;
declare var_s double (19,10);
declare var_pr_sss_minus double (19,10) default 0;
declare var_dpv1 datetime;
declare var_sov double (19,10);
declare var_zpt double (19,10);
declare var_nv1 int;
declare var_nv2 int;
declare var_id int;
declare var_type int default 3;
declare var_sov_tp int default 0;
declare var_pr double (19,10) default -1000;
DECLARE doneNV INT DEFAULT 0;
declare var_s1 double (19,10);
declare var_s2 double (19,10);
declare tmp_ double (19,10);
declare var_sov_ms double (19,10);
declare var_s_ms double (19,10);
declare var_sov_ms_ double (19,10);
declare var_sov_tmp double (19,10);

declare c_nv cursor
 for SELECT distinct nv from zpt_NUz where g=param_year and ms=param_month and tn=param_tn group by nv;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET doneNV = 1;

if param_id=16 then 
 select 123 into var_id;
end if;
if param_id=17 then 
 select 124 into var_id;
end if;

select sum(if(isnull(s),0,s)) from (
 select sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200
 ) query into var_sov_ms_;

select sum(if(isnull(s),0,s)) from (
 select sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou>200
 ) query into var_sov_ms;

if param_id=16 or param_id=17 then
 select sum(if(isnull(s),0,s)) from (
 select sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou<=200
 union 
 select -sum(if(isnull(s),0,s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms<=param_month and (spr_zpt_otchis1.id=param_id or spr_zpt_otchis1.id=var_id) and tn=param_tn and spr_zpt_otchis1.kou>200
 ) query into var_sov;
else
 select var_sov_ms into var_sov;
end if;

select var_sov_ms into var_s1;

if var_sov>600000 then
 select 2 into var_sov_tp;
 select 600000 into var_pr_sss_minus;
 if param_gr<=1966 then
  select 1 into var_type;
  if param_id=16 or param_id=123 then
   select 0 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 0 into var_pr;
   end if;
  end if;
 else if param_gr>=1967 then
  select 2 into var_type;
  if param_id=16 or param_id=123 then
   select 0 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 0 into var_pr;
   end if;
  end if;
 end if;
end if;
 
 select var_pr_sss_minus - (var_sov - var_sov_ms) into var_s1;
 select var_sov_ms - var_s1 into var_s2;

select round(var_s2*var_pr/100,var_points) into var_zpt;

if param_id=16 or param_id=17 then
 select var_zpt-if(isnull(sum(s2)),0,sum(s2)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=var_sov_tp into var_pu_mes;
 else
 select var_zpt into var_pu_mes;
end if;

if var_pu_mes<>0 or 1=1 then
 select var_sov_ms into var_sov;
 select sum(s1) from zpt_per where tn=param_tn and g=param_year and ms<param_month and npp=param_id and sov_tp=var_sov_tp into tmp_;
 select var_s2-if(isnull(tmp_),0,tmp_) into var_s2; 
 select var_s2 into var_sov_tmp;
 
select var_pu_mes into var_yet;
open c_nv;
repeat
 fetch c_nv into var_nv;
 IF NOT doneNV THEN
  select if(isnull(sum(s)),0,sum(s)) from (
  select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
  union 
  select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
  ) query into var_zpt;

  select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;

  if var_s<>0 or 1=1 then
   insert into zpt_per (g,ms,npp,tn,nv,s1,s2,tp,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s2,var_s,var_type,var_sov_tp);
   select var_yet-var_s into var_yet; 
   select var_sov_tmp-var_zpt into var_sov_tmp; 
   select var_nv into var_nv1;
  end if;
 end if; 
 UNTIL doneNV END REPEAT;
CLOSE c_nv;
set doneNV=0;
if var_yet<>0 then
  update zpt_per set s2=s2+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=var_sov_tp;
end if;

end if;


select var_pr_sss_minus into var_sov;

end if; /*600000*/


if var_sov>280000 and var_sov<=600000 then
 select 1 into var_sov_tp;
 select 280000 into var_pr_sss_minus;
 if param_gr<=1966 then
  select 1 into var_type;
  if param_id=16 or param_id=123 then
   select 5.5 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 0 into var_pr;
   end if;
  end if;
 else if param_gr>=1967 then
  select 2 into var_type;
  if param_id=16 or param_id=123 then
   select 3.1 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 2.4 into var_pr;
   end if;
  end if;
 end if;
end if;
 
 select var_pr_sss_minus - (var_sov - var_sov_ms) into var_s1;
 select var_sov_ms - var_s1 into var_s2;

select round(var_s2*var_pr/100,var_points) into var_zpt;

if param_id=16 or param_id=17 then
 select var_zpt-if(isnull(sum(s2)),0,sum(s2)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=var_sov_tp into var_pu_mes;
 else
 select var_zpt into var_pu_mes;
end if;

if var_pu_mes<>0 then
 select var_sov_ms into var_sov;
select sum(s1) from zpt_per where tn=param_tn and g=param_year and ms<param_month and npp=param_id and sov_tp=var_sov_tp into tmp_;
select var_s2-if(isnull(tmp_),0,tmp_) into var_s2; 
select var_s2 into var_sov_tmp;
 
select var_pu_mes into var_yet;
open c_nv;
repeat
 fetch c_nv into var_nv;
 IF NOT doneNV THEN
  select if(isnull(sum(s)),0,sum(s)) from (
  select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
  union 
  select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
  ) query into var_zpt;

  select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;

  if var_s<>0 then
   insert into zpt_per (g,ms,npp,tn,nv,s1,s2,tp,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s2,var_s,var_type,var_sov_tp);
   select var_yet-var_s into var_yet; 
   select var_sov_tmp-var_zpt into var_sov_tmp; 
   select var_nv into var_nv1;
  end if;
 end if; 
 UNTIL doneNV END REPEAT;
CLOSE c_nv;
set doneNV=0;
if var_yet<>0 then
  update zpt_per set s2=s2+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=var_sov_tp;
end if;

end if;


select var_pr_sss_minus into var_sov;

end if; /*280000*/






 if param_gr<=1966 then
  select 1 into var_type;
  if param_id=16 or param_id=123 then
   select 14 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 0 into var_pr;
   end if;
  end if;
 else if param_gr>=1967 then
  select 2 into var_type;
  if param_id=16 or param_id=123 then
   select 8 into var_pr;
  else
   if param_id=17 or param_id=124 then
    select 6 into var_pr;
   end if;
  end if;
 end if;
 end if;
select round(var_sov*var_pr/100,var_points) into var_zpt;

if param_id=16 or param_id=17 then
 select var_zpt-if(isnull(sum(s2)),0,sum(s2)) from zpt_per where g=param_year and ms<=param_month and (npp=param_id or npp=var_id) and tn=param_tn and sov_tp=0 into var_pu_mes;
 else
 select var_zpt into var_pu_mes;
end if;

if var_pu_mes<>0 then
 select var_sov_ms into var_sov;
 select var_s1 into var_sov_tmp;

select var_pu_mes into var_yet;
open c_nv;
repeat
 fetch c_nv into var_nv;
 IF NOT doneNV THEN
  select if(isnull(sum(s)),0,sum(s)) from (
  select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou<=200 and nv=var_nv
  union 
  select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_zpt_otchis1 on zpt_NUz.kou=spr_zpt_otchis1.kou where g=param_year and ms=param_month and spr_zpt_otchis1.id=param_id and tn=param_tn and spr_zpt_otchis1.kou>200 and nv=var_nv
  ) query into var_zpt;

  select round(var_pu_mes*var_zpt/var_sov_ms_,var_points) into var_s;


   select round(var_s1*var_zpt/var_sov_ms_,2) into var_s_ms;


  
  if var_s<>0 then
   insert into zpt_per (g,ms,npp,tn,nv,s1,s2,tp,sov_tp) values (param_year,param_month,param_id,param_tn,var_nv,var_s_ms,var_s,var_type,0);
   select var_yet-var_s into var_yet; 
   select var_sov_tmp-var_zpt into var_sov_tmp; 
  select var_nv into var_nv1;
  end if;
 end if; 
 UNTIL doneNV END REPEAT;
CLOSE c_nv;

if var_yet<>0 then
  update zpt_per set s2=s2+var_yet where g=param_year and ms=param_month and tn=param_tn and nv=var_nv and npp=param_id and sov_tp=0;
end if;
end if;
end$

drop procedure if exists sp_zpt_rn_sd_yet;
CREATE PROCEDURE sp_zpt_rn_sd_yet(in param_year int, in param_month int, in param_ProjectType int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA
READS SQL DATA

BEGIN
declare var_VRED double (19,10);
declare var_sov double (19,10);
declare var_id int;
declare var_nv int;
declare var_nv1 int;
declare var_nv2 int;
declare var_kou int;
declare var_tn int;
declare var_npp int;
declare var_Rlech double (19,10);
declare var_Rdet double (19,10);
declare var_Rort double (19,10);
declare var_yet double (19,10);
declare var_yet1 double (19,10);
declare var_yet2 double (19,10);
declare var_yet3 double (19,10);
declare var_s double (19,10);
declare var_count int;
declare var_prd int;
declare var_kat int;
declare var_vr double (19,10);
declare var_kty double (19,10);
declare var_kof double (19,10);
declare var_zpt double (19,10);
declare var_D2 datetime;
declare var_l double (19,10);
declare var_D1 datetime;
declare var_dn double (19,10);
declare var_kal double (19,10);
declare var_dnpl double (19,10);
declare var_minZPT double (19,10);
declare var_pr double (19,10);
declare var_dpv1 datetime;
declare var_dpv2 datetime;
declare var_lg_det int;
declare var_one int;
declare var_sv double (19,10);
declare var_ov double (19,10);
declare var_r_sov double (19,10);
declare var_r_lg double (19,10);
declare var_r_ud double (19,10);

DECLARE done INT DEFAULT 0;

declare c_nv cursor
for SELECT distinct nv from zpt_NUz where g=param_year and ms=param_month;
declare c_tn cursor
for SELECT distinct tn from zpt_bux where osn=0 group by tn;
declare c_tn0 cursor
for SELECT distinct tn from zpt_bux where osn=1 group by tn having count(osn)>1;

declare c_tn1 cursor
 for select spr_zpt_dol1.nv,zpt_trv_dol.tn,zpt_trv_dol.npp,round(zpt_trv_dol.dn/spr_kl1.dn*okl,2) as s,zpt_bux.kou from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_kl1 on spr_kl1.id=zpt_bux.kl and spr_kl1.g=zpt_trv_dol.g and spr_kl1.ms=zpt_trv_dol.ms
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_vou on spr_vou.id=zpt_bux.kou
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and spr_vou.alg=2 and spr_zpt_dol1.okl>0;

declare c_tn2 cursor
 for select spr_zpt_dol1.nv,zpt_trv_dol.tn,zpt_trv_dol.npp,round(zpt_trv_dol.dn/spr_kl1.dn*(if(isnull(okl),0,okl)+(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*if(isnull(vr),0,0)/100)),2) as s,zpt_bux.kou from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_kl1 on spr_kl1.id=zpt_bux.kl and spr_kl1.g=zpt_trv_dol.g and spr_kl1.ms=zpt_trv_dol.ms
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_vou on spr_vou.id=zpt_bux.kou
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and spr_vou.alg=2 and spr_zpt_dol1.okl>0;

declare c_tn3 cursor
 for select zpt_povr.tn,zpt_povr.npp,zpt_povr.kou,zpt_povr.nv,((okl+(okl*if(isnull(vr),0,0)/100))/plch*ch)*(if(isnull(pr),100,pr)/100) as s from zpt_povr
 left join zpt_bux on zpt_bux.tn=zpt_povr.tn and zpt_bux.npp=zpt_povr.npp
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_povr.nv
 where zpt_povr.g=param_year and zpt_povr.ms=param_month;

declare c_tn4 cursor
for select zpt_povr0.tn,zpt_povr0.npp,zpt_povr0.kou,zpt_povr0.nv,round(if(isnull(ch1),0,ch1)*okl/tar1,2)+round(if(isnull(ch2),0,ch2)*okl/tar2,2) as s from zpt_povr0
left join zpt_bux on zpt_bux.tn=zpt_povr0.tn and zpt_bux.npp=zpt_povr0.npp
left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_povr0.nv
where zpt_povr0.g=param_year and zpt_povr0.ms=param_month;
declare c_tn5 cursor
for select zpt_noch.tn,zpt_noch.npp,spr_zpt_dol1.nv,round(ch*nch,2) as s,zpt_noch.kou from zpt_noch
left join zpt_bux on zpt_bux.tn=zpt_noch.tn and zpt_bux.npp=zpt_noch.npp
left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
where zpt_noch.g=param_year and zpt_noch.ms=param_month and nch>0;
declare c_tn6 cursor
for select tn,npp,nv,kou,s from zpt_proch_vip
where g=param_year and ms=param_month and s<>0;
declare c_tn7 cursor
for select zpt_bol2.tn,zpt_bol.npp_bux,zpt_bol2.nv,zpt_bol.vou,zpt_bol2.s from zpt_bol2
left join zpt_bol on zpt_bol.g=zpt_bol2.g and zpt_bol.ms=zpt_bol2.ms and zpt_bol.tn=zpt_bol2.tn and zpt_bol.npp=zpt_bol2.npp
where zpt_bol2.g=param_year and zpt_bol2.ms=param_month and s>0;
declare c_tn8 cursor
for select zpt_otpusk1.tn,zpt_bux.npp,zpt_otpusk1.nv,zpt_otpusk1.kou,zpt_otpusk1.s from zpt_otpusk1
left join zpt_bux on zpt_bux.tn=zpt_otpusk1.tn and zpt_bux.osn=1
where zpt_otpusk1.g=param_year and zpt_otpusk1.ms=param_month and s>0;

declare c_tn111 cursor
for select zpt_trv_chas.tn,zpt_trv_chas.npp,spr_zpt_dol1.nv,if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_chas.ch/zpt_trv_chas.pl*vr/100 from zpt_trv_chas
 left join zpt_bux on zpt_bux.tn=zpt_trv_chas.tn and zpt_bux.npp=zpt_trv_chas.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_trv_chas.nv
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 where zpt_trv_chas.g=param_year and zpt_trv_chas.ms=param_month and zpt_bux.staj=1;

declare c_tn111_2 cursor
for select zpt_trv_chas.tn,zpt_trv_chas.npp,spr_zpt_dol1.nv,if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_chas.ch/zpt_trv_chas.pl*vr2/100 from zpt_trv_chas
 left join zpt_bux on zpt_bux.tn=zpt_trv_chas.tn and zpt_bux.npp=zpt_trv_chas.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_trv_chas.nv
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 where zpt_trv_chas.g=param_year and zpt_trv_chas.ms=param_month and zpt_bux.staj=1;

declare c_tn111_3 cursor
for select zpt_trv_chas.tn,zpt_trv_chas.npp,spr_zpt_dol1.nv,if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_chas.ch/zpt_trv_chas.pl*vr3/100 from zpt_trv_chas
 left join zpt_bux on zpt_bux.tn=zpt_trv_chas.tn and zpt_bux.npp=zpt_trv_chas.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_trv_chas.nv
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 where zpt_trv_chas.g=param_year and zpt_trv_chas.ms=param_month and zpt_bux.staj=1;

declare c_tn111_4 cursor
for select zpt_trv_chas.tn,zpt_trv_chas.npp,spr_zpt_dol1.nv,if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_chas.ch/zpt_trv_chas.pl*vr4/100 from zpt_trv_chas
 left join zpt_bux on zpt_bux.tn=zpt_trv_chas.tn and zpt_bux.npp=zpt_trv_chas.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_trv_chas.nv
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 where zpt_trv_chas.g=param_year and zpt_trv_chas.ms=param_month and zpt_bux.staj=1;

declare c_tn11 cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn*if(isnull(vr),0,vr)/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and zpt_bux.staj=0;

declare c_tn11_ cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn*if(isnull(vr2),0,vr2)/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and zpt_bux.staj=0;

declare c_tn11_3 cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn*if(isnull(vr3),0,vr3)/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and zpt_bux.staj=0;

declare c_tn11_4 cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn*if(isnull(vr4),0,vr4)/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and zpt_bux.staj=0;

declare c_tn12 cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round((if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn+if(isnull(zpt_NU.s),0,zpt_NU.s))*stj/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp and zpt_bux.osn=1
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 left join zpt_NU on zpt_NU.g=param_year and zpt_NU.ms=param_month and zpt_NU.nv=spr_zpt_dol1.nv and zpt_NU.tn=zpt_bux.tn and zpt_NU.npp=zpt_bux.npp and zpt_NU.kou=20 and 0=1
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month;

declare c_tn13 cursor
for select zpt_trv_dol.tn,zpt_trv_dol.npp,spr_zpt_dol1.nv,round((if(isnull(if(isnull(okl2),if(isnull(okl),0,okl),okl2)+(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*if(isnull(vr),0,0)/100)),0,if(isnull(okl2),if(isnull(okl),0,okl),okl2)+(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*if(isnull(vr),0,0))/100)*zpt_trv_dol.dn/spr_kl1.dn)*stj/100,2) from zpt_trv_dol
 left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
 left join tn on tn.tn=zpt_trv_dol.tn
 where zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month and zpt_bux.staj=0;

declare c_tn14 cursor
for select zpt_trv_chas.tn,zpt_trv_chas.npp,zpt_trv_chas.nv,round((if(isnull(if(isnull(okl2),if(isnull(okl),0,okl),okl2)+(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*if(isnull(vr),0,0)/100)),0,if(isnull(okl2),if(isnull(okl),0,okl),okl2)+(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*if(isnull(vr),0,0)/100))*zpt_trv_chas.ch/zpt_trv_chas.pl)*stj/100,2) from zpt_trv_chas
 left join zpt_bux on zpt_bux.tn=zpt_trv_chas.tn and zpt_bux.npp=zpt_trv_chas.npp
 left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_trv_chas.nv
 left join spr_zpt_dol on spr_zpt_dol.id=zpt_bux.dol
 left join tn on tn.tn=zpt_trv_chas.tn
 where zpt_trv_chas.g=param_year and zpt_trv_chas.ms=param_month and zpt_bux.staj=1;

declare c_tn15 cursor
for select tn,npp,nv,kou,pr from zpt_dopl
where (d1 is null or (d1 is not null and d1<=var_dpv2)) and (d2 is null or (d2 is not null and d2>=var_dpv1)) and pr>0;

declare c_tn16 cursor
for select zpt_dopl.tn,zpt_dopl.npp,nv,zpt_dopl.kou,s,zpt_trv_dol.dn,spr_kl1.dn from zpt_dopl
left join zpt_trv_dol on zpt_trv_dol.tn=zpt_dopl.tn and zpt_trv_dol.npp=zpt_dopl.npp and zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month
left join zpt_bux on zpt_bux.tn=zpt_dopl.tn and zpt_bux.npp=zpt_dopl.npp
left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
where (zpt_dopl.d1 is null or (zpt_dopl.d1 is not null and zpt_dopl.d1<=var_dpv2)) and (zpt_dopl.d2 is null or (zpt_dopl.d2 is not null and zpt_dopl.d2>=var_dpv1)) and s>0 and zpt_trv_dol.dn>0 and spr_kl1.dn>0;

declare c_tn17 cursor
for select zpt_doplz.tn,zpt_doplz.npp,nv,zpt_doplz.kou,s from zpt_doplz
where (zpt_doplz.d1 is null or (zpt_doplz.d1 is not null and zpt_doplz.d1<=var_dpv2)) and (zpt_doplz.d2 is null or (zpt_doplz.d2 is not null and zpt_doplz.d2>=var_dpv1)) and s>0;

declare c_tn18 cursor
for select zpt_sred1.tn,zpt_bux.npp,zpt_sred1.nv,zpt_sred1.kou,zpt_sred1.s from zpt_sred1
left join zpt_bux on zpt_bux.tn=zpt_sred1.tn and zpt_bux.osn=1
where zpt_sred1.g=param_year and zpt_sred1.ms=param_month and zpt_sred1.s>0;

declare c_tn19 cursor
for select zpt_kty.kou,zpt_trv_dol.tn,zpt_trv_dol.npp,zpt_kty.nv,round(kty*round(if(isnull(okl2),if(isnull(okl),0,okl),okl2)*zpt_trv_dol.dn/spr_kl1.dn,2),2) from zpt_trv_dol
left join zpt_kty on zpt_kty.tn=zpt_trv_dol.tn and zpt_kty.npp=zpt_trv_dol.npp and zpt_kty.g=zpt_trv_dol.g and zpt_kty.ms=zpt_trv_dol.ms
left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_kty.nv
left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
where zpt_kty.g=param_year and zpt_kty.ms=param_month;

declare c_tn19_ cursor
for select zpt_kty.kou,zpt_trv_dol.tn,zpt_trv_dol.npp,zpt_kty.nv,round(kty*round(if(isnull(okl),if(isnull(okl),0,okl),okl)*zpt_trv_dol.dn/spr_kl1.dn,2),2) from zpt_trv_dol
left join zpt_kty on zpt_kty.tn=zpt_trv_dol.tn and zpt_kty.npp=zpt_trv_dol.npp and zpt_kty.g=zpt_trv_dol.g and zpt_kty.ms=zpt_trv_dol.ms
left join zpt_bux on zpt_bux.tn=zpt_trv_dol.tn and zpt_bux.npp=zpt_trv_dol.npp
left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_kty.nv
left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
where zpt_kty.g=param_year and zpt_kty.ms=param_month;

declare c_tn29 cursor
for select zpt_dopl.tn,zpt_dopl.npp,zpt_dopl.nv,zpt_dopl.kou,round(zpt_trv_dol.dn*(pr_okl*if(isnull(okl2),if(isnull(okl),0,okl),okl2)/100)/spr_kl1.dn,2) as s from zpt_dopl
left join zpt_trv_dol on zpt_trv_dol.tn=zpt_dopl.tn and zpt_trv_dol.npp=zpt_dopl.npp
and zpt_trv_dol.g=param_year and zpt_trv_dol.ms=param_month
left join zpt_bux on zpt_bux.tn=zpt_dopl.tn and zpt_bux.npp=zpt_dopl.npp
left join spr_kl1 on spr_kl1.g=param_year and spr_kl1.ms=param_month and spr_kl1.id=zpt_bux.kl
left join spr_zpt_dol1 on spr_zpt_dol1.id=zpt_bux.dol and spr_zpt_dol1.nv=zpt_dopl.nv
where (zpt_dopl.d1 is null or (zpt_dopl.d1 is not null and zpt_dopl.d1<=var_dpv2))
 and (zpt_dopl.d2 is null or (zpt_dopl.d2 is not null and zpt_dopl.d2>=var_dpv1)) 
 and zpt_trv_dol.dn>0 and spr_kl1.dn>0;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

/*start transaction;*/

select 100 into var_minZPT;
select d1 from otp where g=param_year and ms=param_month into var_dpv1;
select d2 from otp where g=param_year and ms=param_month into var_dpv2;


SET done = 0;
open c_tn;
 REPEAT
  fetch c_tn into var_tn;
  IF NOT done THEN
   select count(npp) from zpt_bux where tn=var_tn and osn=1 into var_npp;
   if var_npp=0 then 
    select npp from zpt_bux where tn=var_tn limit 1 into var_npp;
    update zpt_bux set osn=0 where tn=var_tn and npp<>var_npp;
    update zpt_bux set osn=1 where tn=var_tn and npp=var_npp;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn;

SET done = 0;
open c_tn0;
 REPEAT
  fetch c_tn0 into var_tn;
  IF NOT done THEN
   select npp from zpt_bux where tn=var_tn and osn=1 limit 1 into var_npp;
   update zpt_bux set osn=0 where tn=var_tn and npp<>var_npp;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn0;

/* 'повременная'*/
SET done = 0;
/*всем кроме кожвена*/
if param_ProjectType<>3 then
 open c_tn1;
 REPEAT
  fetch c_tn1 into var_nv,var_tn,var_npp,var_s,var_kou;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
 CLOSE c_tn1;
end if;

if param_ProjectType=3 then
 open c_tn2;
 REPEAT
  fetch c_tn2 into var_nv,var_tn,var_npp,var_s,var_kou;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
 CLOSE c_tn2;

/*'повременная от плановых часов'*/
SET done = 0;
 open c_tn3;
 REPEAT
 fetch c_tn3 into var_tn,var_npp,var_kou,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
 CLOSE c_tn3;
end if;

/*'повременная для музыкальной'*/
if param_ProjectType=2 then
SET done = 0;
 open c_tn4;
 REPEAT
  fetch c_tn4 into var_tn,var_npp,var_kou,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
 CLOSE c_tn4;
end if;

/*Ночные*/
SET done = 0;
 open c_tn5;
 REPEAT
 fetch c_tn5 into var_tn,var_npp,var_nv,var_s,var_kou;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
 CLOSE c_tn5;

/*Записываем корректировку расчетного листка*/
SET done = 0;
open c_tn6;
 REPEAT
  fetch c_tn6 into var_tn,var_npp,var_nv,var_kou,var_s;
  IF NOT done THEN
   select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
   if var_count>0 then
    update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
   else 
    insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
  end if;
 end if;
UNTIL done END REPEAT;
CLOSE c_tn6;

/*Больничные*/
SET done = 0;
open c_tn7;
 REPEAT
  fetch c_tn7 into var_tn,var_npp,var_nv,var_kou,var_s;
  IF NOT done THEN
   select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
   if var_count>0 then
    update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
   else 
    insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn7;

/*Отпускные*/
SET done = 0;
open c_tn8;
 REPEAT
  fetch c_tn8 into var_tn,var_npp,var_nv,var_kou,var_s;
  IF NOT done THEN
   select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
   if var_count>0 then
    update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
   else 
    insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn8;
update zpt_otpusk set calc=1 where zpt_otpusk.g=param_year and zpt_otpusk.ms=param_month;

select cast(concat(param_year,'-',param_month,'-01') as datetime) into var_d2;
call sp_zpt_rn_sd_yet_10(param_year, param_month, var_d2);

/*доплата за вредность от дней*/
SET done = 0;
 select 20 into var_kou;
 open c_tn11;
 REPEAT
  fetch c_tn11 into var_tn,var_npp,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
	 update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
	 insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
	end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn11;

/*доплата за вредность от часов*/
SET done = 0;
 select 20 into var_kou;
 open c_tn111;
 REPEAT
  fetch c_tn111 into var_tn,var_npp,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
	 update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
	 insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
	end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn111;

if param_ProjectType=1 or param_ProjectType=7 then
 
/*доплата за стаж*/
SET done = 0;
 select 15 into var_kou;
 open c_tn12;
 REPEAT
  fetch c_tn12 into var_tn,var_npp,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
	 update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
	 insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
  UNTIL done END REPEAT;
CLOSE c_tn12;
end if;

if param_ProjectType=3 then
/*доплата за стаж от дней*/
SET done = 0;
 select 15 into var_kou;
 open c_tn13;
 REPEAT
  fetch c_tn13 into var_tn,var_npp,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
	 update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
	 insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
  UNTIL done END REPEAT;
CLOSE c_tn13;

/*доплата за стаж от часов*/
SET done = 0;
 select 15 into var_kou;
 open c_tn14;
 REPEAT
  fetch c_tn14 into var_tn,var_npp,var_nv,var_s;
  IF NOT done THEN
   if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
	 update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
	 insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
  UNTIL done END REPEAT;
CLOSE c_tn14;
end if;


/*доплата за категорию от дней*/
 SET done = 0;
 select id from spr_vou where alg=40 into var_kou;
 if var_kou>0 then
  open c_tn11_;
  REPEAT
   fetch c_tn11_ into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn11_;
 end if;
 
/*доплата за категорию от часов*/
SET done = 0;
 select id from spr_vou where alg=40 into var_kou;
 if var_kou>0 then
  open c_tn111_2;
  REPEAT
   fetch c_tn111_2 into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn111_2;
 end if;



/*доплата участковых от дней*/
 SET done = 0;
 select id from spr_vou where alg=41 into var_kou;
 if var_kou>0 then
  open c_tn11_3;
  REPEAT
   fetch c_tn11_3 into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn11_3;
 end if;

/*доплата участковых от часов*/
 SET done = 0;
 select id from spr_vou where alg=41 into var_kou;
 if var_kou>0 then
  open c_tn111_3;
  REPEAT
   fetch c_tn111_3 into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn111_3;
 end if;
 
/*----доплата от дней*/
 SET done = 0;
 select id from spr_vou where alg=42 into var_kou;
 if var_kou>0 then
  open c_tn11_4;
  REPEAT
   fetch c_tn11_4 into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn11_4;
 end if;
 
/*----доплата от часов*/
 SET done = 0;
 select id from spr_vou where alg=42 into var_kou;
 if var_kou>0 then
  open c_tn111_4;
  REPEAT
   fetch c_tn111_4 into var_tn,var_npp,var_nv,var_s;
   IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
 	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
 	 end if;
    end if;
   end if;
  UNTIL done END REPEAT;
  CLOSE c_tn111_4;
 end if;



/*Доплаты по отработанным дням в процентах*/
SET done = 0;
open c_tn15;
 REPEAT
 fetch c_tn15 into var_tn,var_npp,var_nv,var_kou,var_pr;
  IF NOT done THEN
   select sum(if(isnull(s),0,s)) from (
    select if(isnull(sum(s)),0,sum(s)) as s from zpt_NU left join spr_tvx on spr_tvx.id=zpt_NU.kou where zpt_NU.g=param_year and zpt_NU.ms=param_month and spr_tvx.kou=var_kou and zpt_NU.tn=var_tn and zpt_NU.npp=var_npp and zpt_NU.nv=var_nv and zpt_NU.kou<=200
    union
    select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NU left join spr_tvx on spr_tvx.id=zpt_NU.kou where zpt_NU.g=param_year and zpt_NU.ms=param_month and spr_tvx.kou=var_kou and zpt_NU.tn=var_tn and zpt_NU.npp=var_npp and zpt_NU.nv=var_nv and zpt_NU.kou>200
   ) q into var_zpt;
   if var_zpt>0 then
    select round(var_zpt*var_pr/100,2) into var_s;
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
     end if;
    end if;
  end if;
 end if;
 UNTIL done END REPEAT;
CLOSE c_tn15;

/*Доплаты по отработанным дням в % от оклада*/
SET done = 0;
open c_tn29;
 REPEAT
 fetch c_tn29 into var_tn,var_npp,var_nv,var_kou,var_s;
 IF NOT done THEN
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
     end if;
    end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn29;

/*Доплаты по отработанным дням по сумме*/
SET done = 0;
open c_tn16;
 REPEAT
 fetch c_tn16 into var_tn,var_npp,var_nv,var_kou,var_pr,var_dn,var_dnpl;
 IF NOT done THEN
  select round(var_dn*var_pr/var_dnpl,2) into var_s;
    if var_s>0 then
     select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
     if var_count>0 then
	  update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
     else
	  insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
     end if;
    end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn16;

/*Доплаты всегда*/
SET done = 0;
open c_tn17;
 REPEAT
 fetch c_tn17 into var_tn,var_npp,var_nv,var_kou,var_s;
 IF NOT done THEN
  if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn17;

/*доплаты по средней*/
SET done = 0;
open c_tn18;
 REPEAT
 fetch c_tn18 into var_tn,var_npp,var_nv,var_kou,var_s;
 IF NOT done THEN
  if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn18;

if param_ProjectType=1 then
SET done = 0;
open c_tn19;
 REPEAT
 fetch c_tn19 into var_kou,var_tn,var_npp,var_nv,var_s;
 IF NOT done THEN
  if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn19;
end if;

if param_ProjectType=3 then
SET done = 0;
open c_tn19_;
 REPEAT
 fetch c_tn19_ into var_kou,var_tn,var_npp,var_nv,var_s;
 IF NOT done THEN
  if var_s>0 then
    select count(*) from zpt_NU where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp into var_count;
    if var_count>0 then
     update zpt_NU set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn and npp=var_npp;
    else
     insert into zpt_NU (g,ms,nv,kou,tn,npp,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_npp,var_s);
    end if;
   end if;
  end if;
 UNTIL done END REPEAT;
CLOSE c_tn19_;
end if;

END$



drop procedure if exists sp_zpt_ru;
CREATE PROCEDURE sp_zpt_ru(in param_year int, in param_month int, in param_ProjectType int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA
READS SQL DATA

BEGIN
declare var_VRED double (19,10);
declare var_sov double (19,10);
declare var_id int;
declare var_nv int;
declare var_nv1 int;
declare var_nv2 int;
declare var_kou int;
declare var_tn int;
declare var_npp int;
declare var_Rlech double (19,10);
declare var_Rdet double (19,10);
declare var_Rort double (19,10);
declare var_yet double (19,10);
declare var_yet1 double (19,10);
declare var_yet2 double (19,10);
declare var_yet3 double (19,10);
declare var_s double (19,10);
declare var_count int;
declare var_prd int;
declare var_kat int;
declare var_vr double (19,10);
declare var_kty double (19,10);
declare var_kof double (19,10);
declare var_zpt double (19,10);
declare var_D2 datetime;
declare var_l double (19,10);
declare var_D1 datetime;
declare var_dn double (19,10);
declare var_kal double (19,10);
declare var_dnpl double (19,10);
declare var_minZPT double (19,10);
declare var_pr double (19,10);
declare var_dpv1 datetime;
declare var_dpv2 datetime;
declare var_lg_det int;
declare var_one int;
declare var_sv double (19,10);
declare var_ov double (19,10);
declare var_r_sov double (19,10);
declare var_r_lg double (19,10);
declare var_r_ud double (19,10);

DECLARE done INT DEFAULT 0;

declare c_nv cursor
for SELECT distinct nv from zpt_NUz where g=param_year and ms=param_month;
declare c_tn20 cursor
for select distinct zpt_NUz.tn from zpt_NUz
left join tn on tn.tn=zpt_NUz.tn
where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and prof=1;

declare c_tn30 cursor
 for SELECT distinct id from spr_vou
 where alg=31 or alg=32;

declare c_tn21 cursor
for select distinct zpt_NUz.tn,prd,kat,if(isnull(zpt_doxod.sov),0,zpt_doxod.sov),if(isnull(zpt_doxod.lg),0,zpt_doxod.lg),if(isnull(zpt_doxod.ud),0,zpt_doxod.ud) from zpt_NUz
left join tn on tn.tn=zpt_NUz.tn
left join zpt_doxod on zpt_doxod.tn=zpt_NUz.tn and zpt_doxod.g=param_year
where zpt_NUz.g=param_year and zpt_NUz.ms<=param_month;
declare c_tn22 cursor
for select tn,nv,sum(s) from (
select tn,nv,if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and zpt_NUz.kou<=200 group by tn,nv
union
select tn,nv,-if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and zpt_NUz.kou>200 group by tn,nv
) q  group by tn,nv having sum(s)<0;
declare c_tn23 cursor
for select tn,npp,kou,s,if(isnull(sv),0,sv) from zpt_isp where pu=1 and s>0;
declare c_tn24 cursor
for select tn,npp,kou,pr,if(isnull(sv),0,sv) from zpt_isp where pu=1 and pr>0 and one=1;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

/*start transaction;*/

select 100 into var_minZPT;
select d1 from otp where g=param_year and ms=param_month into var_dpv1;
select d2 from otp where g=param_year and ms=param_month into var_dpv2;

/*'Перекидываем zpt_NU в zpt_NUz'*/
insert into zpt_NUz (g,ms,tn,nv,kou,s) select g,ms,tn,nv,kou,if(isnull(sum(s)),0,sum(s)) 
from zpt_NU where g=param_year and ms=param_month group by g,ms,tn,nv,kou;
/*'Перекидываем zpt_NUz в zpt_M'*/
insert into zpt_M (g,ms,tn) select g,ms,tn
from zpt_NUz where g=param_year and ms=param_month group by g,ms,tn;

/********************************** УДЕРЖАНИЯ  ***************************************/
/*Удерживаем профвзносы и прочую хрень*/
SET done = 0;
open c_tn20;
REPEAT
 fetch c_tn20 into var_tn;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_11(param_year, param_month, var_tn, param_ProjectType, 206, 1);
 end if; 
UNTIL done END REPEAT;
CLOSE c_tn20;

/*Удерживаем п/н 226*/
SET done = 0;
open c_tn30;
REPEAT
 fetch c_tn30 into var_kou;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_11_(param_year, param_month, var_tn, param_ProjectType, var_kou, 13);
 end if; 
UNTIL done END REPEAT;
CLOSE c_tn30;

/*Удерживаем п/н*/
SET done = 0;
open c_tn21;
REPEAT
fetch c_tn21 into var_tn,var_prd,var_kat,var_r_sov,var_r_lg,var_r_ud;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_12(param_year, param_month, var_tn, var_prd, var_kat, var_r_sov, var_r_lg, var_r_ud);
 end if; 
UNTIL done END REPEAT;
CLOSE c_tn21;

/*пробуем удалить минусы*/
SET done = 0;
open c_tn22;
REPEAT
fetch c_tn22 into var_tn,var_nv,var_s;
 IF NOT done THEN
  call sp_zpt_minusy(param_year,param_month,var_tn,var_nv,var_s,206);  
  call sp_zpt_minusy(param_year,param_month,var_tn,var_nv,var_s,211);
 end if; /*IF NOT done THEN*/
UNTIL done END REPEAT;
CLOSE c_tn22;

/*исполнительные листы по сумме по одной ведомости*/
SET done = 0;
open c_tn23;
repeat
fetch c_tn23 into var_tn,var_npp,var_kou,var_s,var_sv;
IF NOT done THEN
 select if(isnull(sum(s)),0,sum(s)) from zpt_NUz where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and zpt_NUz.tn=var_tn and zpt_NUz.kou<=200 into var_yet;
 if var_yet>0 then
/*определяем по какой ведомости больше всего начислено*/
  select nv from
  (
   select nv,max(s) as s from
   (
    select nv,if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz
    where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and zpt_NUz.tn=var_tn and zpt_NUz.kou<=200
    group by nv 
   ) q  group by nv
    order by q.s desc limit 1
  ) qq  group by nv into var_nv;
  if var_sv>0 then
   select var_sv-if(isnull(sum(s)),0,sum(s)) from zpt_isp1 where tn=var_tn and npp=var_npp into var_ov;
   if var_ov>0 then
    select var_ov-var_s into var_zpt;
    if var_zpt<0 then
	 select var_s+var_zpt into var_s;
	end if; /*if var_zpt<0 then*/
   else 
    select 0 into var_s;
   end if; /*if var_ov>0 then*/
  end if; /*if var_sv>0 then*/
  if var_s>0 then
   select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn into var_count;
   if var_count>0 then
    update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn;
   else
    insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_s);
   end if; /*if var_count>0 then*/
   select count(*) from zpt_isp1 where g=param_year and ms=param_month and tn=var_tn and npp=var_npp into var_count;
   if var_count>0 then
    update zpt_isp1 set s=s+var_s where g=param_year and ms=param_month and tn=var_tn and npp=var_npp;
   else 
    insert into zpt_isp1 (g,ms,tn,npp,s) values (param_year,param_month,var_tn,var_npp,var_s);
   end if; /*if var_count>0 then*/
  end if; /*if var_s>0 then*/
 end if; /*if var_yet>0 then*/
end if; /*IF NOT done THEN*/
UNTIL done END REPEAT;
CLOSE c_tn23;

/*исполнительные листы в процентах по одной ведомости'*/
SET done = 0;
open c_tn24;
repeat
fetch c_tn24 into var_tn,var_npp,var_kou,var_pr,var_sv;
IF NOT done THEN
 select round(sum(if(isnull(s),0,s))*var_pr/100,2) from (
 select if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=var_tn and zpt_NUz.kou<=200 
 union
 select -if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz left join spr_tvx on spr_tvx.id=zpt_NUz.kou where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and spr_tvx.kou=var_kou and zpt_NUz.tn=var_tn and zpt_NUz.kou>200
 ) q into var_s;
 if var_sv>0 then
  select var_sv-if(isnull(sum(s)),0,sum(s)) from zpt_isp1 where tn=var_tn and npp=var_npp into var_ov;
  if var_ov>0 then
   select var_ov-var_s into var_zpt;
   if var_zpt<0 then
    select var_s+var_zpt into var_s;
   end if;
  else 
   select 0 into var_s;
  end if; /*if var_ov>0 then*/
 end if; /*if var_sv>0 then*/
 if var_s>0 then
/*определяем по какой ведомости больше всего начислено*/
  select nv from 
  (
   select nv,max(s) as s from
   (
    select nv,if(isnull(sum(s)),0,sum(s)) as s from zpt_NUz
    where zpt_NUz.g=param_year and zpt_NUz.ms=param_month and zpt_NUz.tn=var_tn and zpt_NUz.kou<=200
    group by nv 
   ) q  group by nv
   order by q.s desc
  ) qq  group by nv limit 1 into var_nv;
  select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn into var_count;
  if var_count>0 then
   update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=var_kou and tn=var_tn;
  else
   insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv,var_kou,var_tn,var_s);
  end if;
  select count(*) from zpt_isp1 where g=param_year and ms=param_month and tn=var_tn and npp=var_npp into var_count;
  if var_count>0 then
   update zpt_isp1 set s=s+var_s where g=param_year and ms=param_month and tn=var_tn and npp=var_npp;
  else 
   insert into zpt_isp1 (g,ms,tn,npp,s) values (param_year,param_month,var_tn,var_npp,var_s);
  end if;
 end if;
end if;
UNTIL done END REPEAT;
CLOSE c_tn24;

/*исполнительные листы в процентах по каждой ведомости*/
SET done = 0;
open c_nv;
repeat
fetch c_nv into var_nv;
IF NOT done THEN
 call sp_zpt_rn_sd_yet_13(param_year, param_month, var_nv);
end if;
UNTIL done END REPEAT;
CLOSE c_nv;

END$


drop procedure if exists sproc_gl_bux_vvod_noos;
create procedure sproc_gl_bux_vvod_noos(param_dpv datetime, param_fil_id int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN

declare  var_os_id int;
declare  var_oper_id int;
declare  var_prov_id int;
declare  var_number int;
declare  var_sch varchar(9);
declare  var_sch_iz varchar(9);
declare  var_prd int;
declare  var_mol int;
declare  var_kbk_deb int;
declare  var_kbk_kred int;
declare  var_kred0 char(1);
declare  var_npp int default 0;
declare  var_first_price decimal(19,2);
declare  var_sum decimal(19,2) default 0;
declare  var_name varchar(255);
declare  var_iznos_all decimal(19,2);
declare  var_kol decimal(19,10);
DECLARE done INT DEFAULT 0;
declare c_osn_sr cursor
 for select spr_osn_sr.id,sch,prd,mol,first_price,name,sch_iz,iznos_all,if(isnull(spr_osn_sr.kol),1,spr_osn_sr.kol),spr_osn_sr.kbk_deb,spr_osn_sr.kbk_kred from spr_osn_sr
left join gl_bux_provodki on gl_bux_provodki.prov_id=spr_osn_sr.prov_id
where sch<>''  and gl_bux_provodki.prov_id is null and d_ekspl<=param_dpv and spr_osn_sr.fil_id=param_fil_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

start transaction;
select if(max(id),max(id)+1,1) from gl_bux_provodki into var_oper_id;
select if(max(number),max(number)+1,1) from gl_bux_oper_by_hand where year(d)=year(param_dpv) into var_number;

insert into gl_bux_oper_by_hand (id,d,number,prim,sys,fil_id) values (var_oper_id,param_dpv,var_number,'Ввод начальных остатков основных средств',1,param_fil_id);

  open c_osn_sr;
  REPEAT
  fetch c_osn_sr into var_os_id,var_sch,var_prd,var_mol,var_first_price,var_name,var_sch_iz,var_iznos_all,var_kol,var_kbk_deb,var_kbk_kred;
  IF NOT done THEN
    select left(var_Sch,1) into var_kred0;

    select var_npp+1 into var_npp;
    insert into gl_bux_provodki (id,npp,deb0,deb1,deb2,deb3,deb_sb1,deb_sb2,deb_sb3,kred0,kred1,kred2,kred3,kol,s,d,prim,fil_id,kbk_deb,kbk_kred) values
    (var_oper_id,var_npp,left(var_Sch,1),SUBSTRING(var_Sch,2,3),SUBSTRING(var_Sch,5,2),'310',var_os_id,var_mol,var_prd,var_kred0,'000','00','000', var_kol,var_first_price,param_dpv,var_name,param_fil_id,var_kbk_deb,var_kbk_deb);

if var_sch_iz<>'' and var_iznos_all>0 then
    select var_npp+1 into var_npp;
    insert into gl_bux_provodki (id,npp,deb0,deb1,deb2,deb3,kred0,kred1,kred2,kred3,kred_sb1,s,d,prim,fil_id,kbk_deb,kbk_kred) values
    (var_oper_id,var_npp,var_kred0,'000','00','000',left(var_Sch_iz,1),SUBSTRING(var_Sch_iz,2,3),SUBSTRING(var_Sch_iz,5,2),'410',var_os_id,var_iznos_all,param_dpv,var_name,param_fil_id,var_kbk_kred,var_kbk_kred);
end if;
    select var_sum+var_first_price into var_sum;
    update spr_osn_sr set prov_id=LAST_INSERT_ID() where id=var_os_id;
  end if;
 UNTIL done END REPEAT;

 CLOSE c_osn_sr;

update gl_bux_oper_by_hand set s=var_sum where id=var_oper_id;

if var_npp>0 then
 commit;
 else
  rollback;
end if;

END$

/*call sproc_gl_bux_vvod_noos('2004-12-1');*/

drop procedure if exists sp_zpt_per;
CREATE PROCEDURE sp_zpt_per(param_year int, param_month int)
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_points int;
declare var_tn int;
declare var_pr double (19,10);
declare var_id int;
declare var_gr int;
DECLARE done INT DEFAULT 0;

declare c_tn25 cursor
 for select distinct tn,id,pr,poits from spr_zpt_otchis
 left join zpt_NUz on 1=1
 where (id=1 or id=3 or id=5 or id=7) and zpt_NUz.g=param_year and zpt_NUz.ms=param_month group by zpt_NUz.tn,id,pr,poits;
declare c_tn28 cursor
 for select distinct tn,id,pr,poits from spr_zpt_otchis
 left join zpt_NUz on 1=1
 where (id=126 or id=128 or id=122 or id=127) and zpt_NUz.g=param_year and zpt_NUz.ms=param_month group by zpt_NUz.tn,id,pr,poits;
declare c_tn26 cursor
 for SELECT distinct zpt_NUz.tn,year(dr) as gr from zpt_NUz
 left join tn on tn.tn=zpt_NUz.tn
 where zpt_NUz.g=param_year and zpt_NUz.ms=param_month group by zpt_NUz.tn,dr;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

delete from zpt_per where g=param_year and ms=param_month;

/*определяем сколько отстегнуть стандартным способом 226*/
SET done = 0;
open c_tn28;
repeat
 fetch c_tn28 into var_tn,var_id,var_pr,var_points;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_14_226(param_year, param_month, var_tn, var_id, var_pr, var_points);
 end if;
UNTIL done END REPEAT;
CLOSE c_tn28;

/*определяем сколько отстегнуть стандартным способом*/
SET done = 0;
open c_tn25;
repeat
 fetch c_tn25 into var_tn,var_id,var_pr,var_points;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_14(param_year, param_month, var_tn, var_id, var_pr, var_points);
 end if;
UNTIL done END REPEAT;
CLOSE c_tn25;

/*определяем сколько отстегнуть страховых и накопительных*/
SET done = 0;
open c_tn26;
repeat
 fetch c_tn26 into var_tn,var_gr;
 IF NOT done THEN
  call sp_zpt_rn_sd_yet_15(param_year, param_month, var_tn, 123, var_gr, 2);
  call sp_zpt_rn_sd_yet_15(param_year, param_month, var_tn, 124, var_gr, 2);
  call sp_zpt_rn_sd_yet_15(param_year, param_month, var_tn, 16, var_gr, 2);
  call sp_zpt_rn_sd_yet_15(param_year, param_month, var_tn, 17, var_gr, 2);
 end if;
UNTIL done END REPEAT;
CLOSE c_tn26;
end$


drop procedure if exists sp_zpt_rn_sd_yet_11_;

CREATE PROCEDURE sp_zpt_rn_sd_yet_11_(param_year int, param_month int, param_tn int, param_ProjectType int, param_kou int, param_pr double (19,10))
LANGUAGE SQL
CONTAINS SQL
MODIFIES SQL DATA

BEGIN
declare var_nv int;
declare var_tn int;
declare var_s double (19,10);
declare var_count int;
DECLARE done0 INT DEFAULT 0;
declare c_tn27 cursor
 for SELECT distinct tn,nv from zpt_NUz
 where g=param_year and ms=param_month group by tn,nv;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done0 = 1;
 SET done0 = 0;
 open c_tn27;
 REPEAT
 fetch c_tn27 into var_tn,var_nv;
  IF NOT done0 THEN
select round(sum(s)*.13,0) as s from
 (
  select zpt_nuz.s,vych_s,round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2) as s_ from
  (
   select spr_tvx.id as idd from spr_vou
   inner join spr_tvx on spr_tvx.kou=spr_vou.id
   where spr_vou.id=param_kou and spr_tvx.id<=200
  ) q_
  left join spr_vou on spr_vou.id=idd
  left join zpt_nuz on zpt_nuz.kou=idd
  where zpt_nuz.g=param_year and zpt_nuz.ms=param_month and zpt_nuz.tn=var_tn and nv=var_nv
  group by ms,nv,zpt_nuz.kou
   having((round(zpt_nuz.s-if(isnull(vych_s),0,vych_s),2)>0 and vych_s>0) or vych_s=0 or vych_s is null)
 ) q__ into var_s;
   if var_s > 0 then
    select count(*) from zpt_NUz where g=param_year and ms=param_month and nv=var_nv and kou=param_kou and tn=var_tn into var_count;
    if var_count>0 then
     update zpt_NUz set s=s+var_s where g=param_year and ms=param_month and nv=var_nv and kou=param_kou and tn=var_tn;
    else
     insert into zpt_NUz (g,ms,nv,kou,tn,s) values (param_year,param_month,var_nv,param_kou,var_tn,var_s);
    end if;
   end if;	
  end if; /*IF NOT done0 THEN*/
 UNTIL done0 END REPEAT;
 CLOSE c_tn27;

end$