📄 sbp_rtacctints.~pas
字号:
{
-----------------------------------------------------------------------------------------
--------------------------------退休个人帐户(不分企业，机关养老)-------------------------
-----------------------------------------------------------------------------------------
--退休个人帐户结息guocng
}
unit sbp_rtacctints;
interface
uses
SysUtils, Classes, DB, DBTables;
function sbf_siyear(ai_year ,ai_mon :integer):integer;
function sbf_get_siisint(ai_year ,as_industry:integer;Db:TDatabase):double;
function sbf_siyearmon(ai_year ,ai_mon :integer;as_bool :char):integer;
procedure rtacctints(al_ps ,ai_year,ai_mon :integer;Db:TDatabase);
implementation
//--当前年月的社保年份
function sbf_siyear(ai_year,ai_mon :integer):integer;
var
ln_mon :integer;
li_year :integer;
li_mon :integer;
begin
//--社保机构记帐起始月份中途修改有问题.无法结息修改前年度
try
ln_mon:=1;
if ai_mon=13 then
begin
li_mon :=1 ;
li_year :=ai_year+1;
end else
begin
li_mon :=ai_mon ;
li_year :=ai_year;
end ;
if li_mon<ln_mon then
li_year :=li_year-1;
Result:=li_year;
Except
Result:= -1;
end;
end;
//--得到指定年度记帐利率
function sbf_get_siisint(ai_year ,as_industry:integer;Db:TDatabase):double;
var
ln_siinter :double;
Query:TQuery;
Begin
ln_siinter:=0;
try
Query:=TQuery.Create(nil);
with Query do
begin
databasename:=Db.DatabaseName ;
sql.Add('Select isnull(siinter,0) from sbds_inancons where yearno=:param1 and industry = :param2');
parambyname('param1').AsInteger :=ai_year;
parambyname('param2').AsInteger :=as_industry;
prepare;
open;
if recordcount>0 then
ln_siinter:=Fields[0].AsFloat ;
close;
end;
except
Result:=ln_siinter;
end;
Result:=ln_siinter;
{Select nvl(siinter,0) into ln_siinter
from sbds_inancons where yearno=ai_year and industry = as_industry;}
{if ln_siinter=0 then
sbk_public.sbp_err('00000000'||ai_year||'年度代码为'||as_industry||'的行业记帐利率为零或空值');
end if;
return ln_siinter;
Exception
when NO_DATA_FOUND then
sbk_public.sbp_err('00000000'||ai_year||'年度代码为'||as_industry||'记帐利率未找到'); }
End;
function sbf_siyearmon(ai_year ,ai_mon :integer;as_bool :char):integer;
var
ln_year :integer ;
ln_mon :integer ;
begin
if as_bool='N' then // --小
begin
ln_year :=sbf_siyear(ai_year,ai_mon);
ln_mon :=1;
Result:=ln_year*12+1;
end else
if as_bool='Y' then //--大
begin
ln_year :=sbf_siyear(ai_year,ai_mon);
Result:=ln_year*12+ai_mon;
end ;
end;
procedure rtacctints(al_ps ,ai_year,ai_mon :integer;Db:TDatabase);
var
ln_starty :integer;//--计算开始年
ln_endy :integer;//--计算开始月
ln_start :integer;
ln_end :integer;
ln_ints :double;
ln_pyfd :double;
ln_ftfd :double;
li_siyear :double;
ln_mons :integer;//--月积数累加值
ln_count :integer;
li_year :integer;//--退休年
li_mon :integer;//--退休月
ln_acctsave :double;//--退休是个人帐户
as_industry :integer;
Query,temp:TQuery;
i:integer;
{Cursor cur_rtacct(an_ps in number,an_year in number) is
select yearno,monthno,nvl(sum(pypsacct),0) pypsacct
from sbda_rtwgpy
where psseno=an_ps and yearno=an_year group by yearno,monthno;}
begin
as_industry:=0;
ln_acctsave:=0;
li_year:=0;
li_mon:=0;
temp:=TQuery.Create(nil);
with temp do
begin
Databasename:=db.DatabaseName ;
close;
sql.Clear ;
sql.Add('select industry from sbdb_cparch where cpseno =(select cpseno from sbdb_rtarch where psseno =:param)');
parambyname('param').AsInteger :=al_ps;
prepare;
open;
if recordcount>0 then
as_industry:=Fields[0].AsInteger ;
close;
sql.Clear ;
sql.Add('delete from sbda_psrtacct where psseno =:param');
parambyname('param').AsInteger :=al_ps;
prepare;
execsql;
close;
sql.Clear ;
sql.Add('select isnull(acctsave,0),cast(rtdate as char(4)),right(cast(rtdate as char(7)),2) from sbdb_rtarch where psseno =:param');
parambyname('param').AsInteger :=al_ps;
prepare;
open;
if recordcount>0 then
begin
ln_acctsave:=Fields[0].AsFloat ;
li_year:=Fields[1].AsInteger ;
li_mon:=Fields[2].AsInteger ;
end;
end;
{select industry into as_industry from sbdb_cparch where cpseno =
(select cpseno from sbdb_rtarch where psseno = al_ps);
delete from sbda_psrtacct where psseno = al_ps;
select nvl(acctsave,0),to_number(to_char(rtdate,'yyyy')),to_number(to_char(rtdate,'mm'))
into ln_acctsave,li_year,li_mon
from sbdb_rtarch where psseno =al_ps;--get start year }
ln_starty :=sbf_siyear(li_year,li_mon);
ln_endy :=sbf_siyear(ai_year,ai_mon);//--get end year
Query:=TQuery.Create(nil);
with Query do
begin
Databasename:=db.DatabaseName ;
close;
sql.Clear ;
sql.Add('select yearno,monthno,nvl(sum(pypsacct),0) pypsacct from sbda_rtwgpy where psseno=:param1 and yearno=:param2 group by yearno,monthno');
parambyname('param1').AsInteger :=al_ps;
parambyname('param2').AsInteger :=ai_year;
prepare;
open;
{Cursor cur_rtacct(an_ps in number,an_year in number) is
select yearno,monthno,nvl(sum(pypsacct),0) pypsacct
from sbda_rtwgpy
where psseno=an_ps and yearno=an_year group by yearno,monthno;}
end;
//for i in ln_starty..ln_endy
for i:=ln_starty to ln_endy do
begin
// --逐年计算个人退休帐户
ln_pyfd:=0;//--当年支付
ln_mons:=0;//--当年月积数
ln_ints:=sbf_get_siisint(i,as_industry,db)/12;
//for c_rt in cur_rtacct(al_ps,i) loop
while not Query.Eof do
begin
ln_start :=sbf_siyearmon(i,Query.Fieldbyname('monthno').asinteger,'N');
if i=ln_endy then
ln_end :=sbf_siyearmon(i,ai_mon,'Y')
else
ln_end :=sbf_siyearmon(i,12,'Y');
if Query.FieldByName('pypsacct').AsFloat >0 then
begin
ln_pyfd :=ln_pyfd+Query.fieldbyname('pypsacct').AsFloat;
ln_mons :=ln_mons+strtoint(formatfloat('0',Query.fieldbyname('pypsacct').asfloat*(ln_end-i*12-Query.FieldByName('monthno').AsInteger+1)));
end ;
Query.Next ;
end; //loop;--取得当年支付与月积数
if i=ln_starty then ln_start :=li_mon
else
ln_start :=1;
ln_count :=-1;
ln_ftfd :=-1;
with temp do
begin
ln_ftfd:=0;
close;
sql.clear;
sql.add('select isnull(sum(lyrefd),0) from sbda_psrtacct where psseno=:param1 and yearno=:param2 and keflag=:param3');
parambyname('param1').asinteger:=al_ps;
parambyname('param2').asinteger:=i;
parambyname('param3').asstring:='0';
prepare;
open;
if recordcount>0 then ln_ftfd:=Fields[0].asfloat;
end;
{ select nvl(sum(lyrefd),0) into ln_ftfd from sbda_psrtacct
where psseno=al_ps and yearno=i and keflag='0';
-- exception --没有sbda_psrtacct记录 }
if not (ln_ftfd>0) then
with temp do
begin
ln_count:=0;
close;
sql.clear;
sql.add('select count(*) from sbda_psrtacct where psseno=:param1 and yearno=:param2');
parambyname('param1').asinteger:=al_ps;
parambyname('param2').asinteger:=i;
prepare;
open;
if recordcount>0 then ln_count:=Fields[0].asinteger;
end;
{select count(*) into ln_count from sbda_psrtacct
where psseno=al_ps and yearno=i;}
if not ln_count>0 then
// --ln_start:=sbf_siyearmon(i,1,'N');
with temp do
begin
close;
sql.clear;
sql.add('insert into sbda_psrtacct(psseno,yearno,lyrefd,cyrtwg,cypymons,cypyints,nmrtwg,intsdate,cyedacct,keflag)');
sql.add('values(:param1,:param2,:param3,:param4,:param4,:param5,:param6,:param7,:param7,:param8,:param9,:param10 )');
parambyname('param1').asinteger:=al_ps;
parambyname('param2').asinteger:=i;
parambyname('param3').asstring:=formatfloat('0.00',ln_acctsave);
parambyname('param4').asstring:=formatfloat('0.00',ln_pyfd);
parambyname('param5').asinteger:=ln_mons;
parambyname('param6').asstring:=formatfloat('0.00',ln_acctsave * ln_ints * 12 - ln_mons*ln_ints);
parambyname('param7').asstring:='0';
//parambyname('param8').asdate:=strtodate(ai_year*10000+ai_mon*100+1,'yyyymmdd'); //有问题,数字与日期的转换
parambyname('param8').AsInteger :=ai_year*10000+ai_mon*100+1;
parambyname('param9').asstring:=formatfloat('0.00',ln_acctsave - ln_pyfd +(ln_acctsave*ln_ints*(ln_end-ln_start+1)-ln_mons*ln_ints));
parambyname('param10').asstring:='0';
prepare;
execsql;
end;
{insert into sbda_psrtacct(prseno,psseno,yearno,lyrefd,cyrtwg,
cypymons,cypyints,nmrtwg,intsdate,cyedacct,keflag)
values(sb_prseno.nextval,al_ps,i,ln_acctsave,ln_pyfd,ln_mons,
ln_acctsave * ln_ints * 12 - ln_mons*ln_ints,0,
to_date(ai_year*10000+ai_mon*100+1,'yyyymmdd'),
ln_acctsave - ln_pyfd +(ln_acctsave*ln_ints*(ln_end-ln_start+1)-ln_mons*ln_ints),'0');
--else
-- update sbda_psrtacct set cyrtwg=ln_pyfd,keflag='5',
-- cypymons=lyrefd*(ln_end-ln_start+1)-ln_mons,
-- cypyints=lyrefd*(ln_end-ln_start+1)*ln_ints-ln_mons*ln_ints,
--cyedacct=lyrefd - ln_pyfd +(lyrefd*(ln_end-ln_start-1)*ln_ints-ln_mons*ln_ints)
-- where psseno=al_ps and yearno=li_siyear and keflag='0'; }
ln_acctsave :=ln_acctsave - ln_pyfd +(ln_acctsave*ln_ints*12-ln_mons*ln_ints);
end ;
end;
end.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -