p_calc_person.sql

一个地方税务征收管理系统

declare @acount_id int
declare @she_id int

select @acount_id=10
select @she_id=1

delete rw  from rw a,v_rw b
  where a.person_id=b.person_id and she_id=@she_id and b.acount_id=@acount_id

insert into rw (acount_id,s1,s2,person_id,td,rq,ldl)
SELECT  @acount_id,null,null,
    person_id, td , rq , ldl
		FROM V_person_she
		WHERE (state=0 and she_id=@she_id)

declare @sum_s1 money
declare @sum_s2 money

declare @r_td_s1 numeric(15,6)
declare @r_rq_s1 numeric(15,6)
declare @r_ldl_s1 numeric(15,6)

declare @r_td_s2 numeric(15,6)
declare @r_rq_s2 numeric(15,6)
declare @r_ldl_s2 numeric(15,6)


declare @temp numeric(15,6)


select @sum_s1=s1,@sum_s2=s2 from rw_she where acount_id=@acount_id  and she_id=@she_id  

select @temp= @sum_s1*s1_td/100 from rw_she where acount_id=@acount_id  and she_id=@she_id

if @temp<>0 begin
    select @r_td_s1=(@sum_s1*s1_td/100)/td from rw_she where acount_id=@acount_id  and she_id=@she_id
  end
  else select @r_td_s1=0



 
 select @temp= @sum_s1*s1_rq/100 from rw_she where acount_id=@acount_id  and she_id=@she_id
   if @temp<>0 begin
       select @r_rq_s1=rq/(@sum_s1*s1_rq/100) from rw_she where acount_id=@acount_id  and she_id=@she_id
   end
   else select @r_rq_s1=0


 select @temp= @sum_s1*s1_ldl/100 from rw_she where acount_id=@acount_id  and she_id=@she_id
   if @temp<>0 begin
       select @r_ldl_s1=ldl/(@sum_s1*s1_ldl/100) from rw_she where acount_id=@acount_id  and she_id=@she_id
    end
  else select @r_ldl_s1=0

select @sum_s2=s1,@sum_s2=s2 from rw_she where acount_id=@acount_id  and she_id=@she_id
  
 select @temp= @sum_s2*s1_td/100 from rw_she where acount_id=@acount_id  and she_id=@she_id
 if @temp<>0 begin
    select @r_td_s2=(@sum_s2*s1_td/100)/td from rw_she where acount_id=@acount_id  and she_id=@she_id
  end
  else select @r_td_s2=0
 
 
 select @temp= @sum_s2*s1_rq/100 from rw_she where acount_id=@acount_id  and she_id=@she_id
   if @temp<>0 begin
       select @r_rq_s2=rq/(@sum_s2*s1_rq/100) from rw_she where acount_id=@acount_id  and she_id=@she_id
   end
   else select @r_rq_s2=0


 select @temp= @sum_s2*s1_ldl/100 from rw_she where acount_id=@acount_id  and she_id=@she_id
   if @temp<>0 begin
       select @r_ldl_s2=ldl/(@sum_s2*s1_ldl/100) from rw_she where acount_id=@acount_id  and she_id=@she_id
    end
  else select @r_ldl_s2=0

/*
update rw set rq=0 
  where rw_id in (select rw_id from v_rw 
                  where rq is null and  she_id=@she_id and acount_id=@acount_id)

update rw set td=0 
  where rw_id in (select rw_id from v_rw 
                  where td is null and  she_id=@she_id and acount_id=@acount_id)

update rw set ldl=0 
   where rw_id in (select rw_id from v_rw 
                  where ldl is null and  she_id=@she_id and acount_id=@acount_id)
*/

update rw set s2=td*@r_td_s2+rq*@r_rq_s2+ldl*@r_ldl_s2 ,
	s1=td*@r_td_s1+rq*@r_rq_s1+ldl*@r_ldl_s1 
    where rw_id in (select rw_id from v_rw 
                  where  she_id=@she_id and acount_id=@acount_id)