PROC SQLSERVER
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER proc sjzh_lc6all(
@xmbh varchar(10),
@kstime varchar(12),
@jstime varchar(12),
@path varchar(200),
@byear integer
)
as
declare @year varchar(12)
declare @tbname varchar(100)
declare @tbnamepzk varchar(100)
declare @tbnamelshspz varchar(100)
declare @tbname1 varchar(100)
declare @tbname2 varchar(100)
set @year=left(ltrim(rtrim(@kstime)),4)
-------------pzb
set @tbname='xt_pzb_' + @xmbh + @year
if @byear=1
begin
set @tbnamepzk='LSpzk1'+@year
set @tbnamelshspz='lshspz'+@year
end
else
begin
set @tbnamepzk='LSpzk1'
set @tbnamelshspz='lshspz'
end
declare @sql varchar(8000)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tbname+']')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @sql='delete from [dbo].[' + @tbname + ']'
print (@sql)
exec (@sql)
end
set @sql=''
set @sql='SELECT A.F_DATE as PZRQ,''统一编号'' + A.F_QTXX + ''内部编号'' + A.F_PZBH as PZBH ,
A.F_PZLX as PZLX , A.F_FDZS as SFPZZS , A.F_JY as ZY,A.F_KMBH as KMBH,B.F_JE as JFJE,0 as DFJE,
B.F_SL as JFSL , 0 as DFSL , A.F_ZDXM as JBR,A.F_SHXM as SHR,A.F_JZXM as ZGR , B.F_BMBH as WLBMDM ,
b.F_xjbh as XJLLMC , B.f_dw_bh as WLDWDM , B.F_zg_bh as WLGRDM , B.F_HSBH as WLXMDM , B.F_CODE as XH ,
B.F_CBBH as bz2 , B.F_CPBH as bz1 , B.F_YSBH as bz3
INTO ##temp_pzb1
FROM OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as
A
left join OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+
@tbnamelshspz +'] as B
on a.f_date =b.f_date and a.f_pzbh=b.F_pzbh and a.F_flbh=b.F_flbh and A.f_kmbh = b.F_kmbh
and A.f_code =b.F_code where A.F_JZFX=''J'' and A.F_DATE<='''+ @jstime +''' and A.F_DATE>='''+ @kstime +''' and b.f_je is not
null '
print (@sql)
exec (@sql)
set @sql=''
set @sql='insert into '
z3)
select '''+ @xmbh+''',pzrq,pzbh,pzlx,SFPZZS,zy,rtrim
(kmbh),jfje,dfje,jfsl,dfsl,JBR,SHR,ZGR,WLBMDM,XJLLMC,WLDWDM,WLGRDM,WLXMDM,XH,bz2,bz1,bz3 from ##temp_pzb1'
print (@sql)
exec (@sql)
drop table ##temp_pzb1
set @sql=''
set @sql='SELECT A.F_DATE as PZRQ,''统一编号'' + A.F_QTXX + ''内部编号'' + A.F_PZBH as PZBH ,A.F_PZLX as PZLX , A.F_FDZS as
SFPZZS ,
A.F_JY as ZY,A.F_KMBH as KMBH,A.F_JE as JFJE,0 as DFJE, A.F_SL as JFSL , 0 as DFSL , A.F_ZDXM as JBR,A.F_SHXM as SHR,
A.F_JZXM as ZGR , B.F_BMBH as WLBMDM , b.F_xjbh as XJLLMC , B.f_dw_bh as WLDWDM , B.F_zg_bh as WLGRDM , B.F_HSBH as WLXMDM ,
A.F_CODE as XH , B.F_CBBH as bz2 , B.F_CPBH as bz1 , B.F_YSBH as bz3
INTO ##temp_pzb2
FROM OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as
A
left join OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+
@tbnamelshspz +'] as B
on a.f_date =b.f_date and a.f_pzbh=b.F_pzbh and a.F_flbh=b.F_flbh and A.f_kmbh = b.F_kmbh
and A.f_code =b.F_code where A.F_JZFX=''J'' and A.F_DATE<='''+ @jstime +''' and A.F_DATE>='''+ @kstime +''' and b.f_je is
null '
print (@sql)
exec (@sql)
set @sql=''
set @sql='insert into '
z3)
select '''+ @xmbh+''',pzrq,pzbh,pzlx,SFPZZS,zy,rtrim
(kmbh),jfje,dfje,jfsl,dfsl,JBR,SHR,ZGR,WLBMDM,XJLLMC,WLDWDM,WLGRDM,WLXMDM,XH,bz2,bz1,bz3 from ##temp_pzb2'
print (@sql)
exec (@sql)
drop table ##temp_pzb2
set @sql=''
set @sql='SELECT A.F_DATE as PZRQ,''统一编号'' + A.F_QTXX + ''内部编号'' + A.F_PZBH as PZBH ,A.F_PZLX as PZLX ,
A.F_FDZS as SFPZZS , A.F_JY as ZY,A.F_KMBH as KMBH,0 as JFJE,B.F_JE as DFJE, 0 as JFSL , B.F_SL as DFSL ,
A.F_ZDXM as JBR,A.F_SHXM as SHR,A.F_JZXM as ZGR , B.F_BMBH as WLBMDM , b.F_xjbh as XJLLMC , B.f_dw_bh as WLDWDM ,
B.F_zg_bh as WLGRDM , B.F_HSBH as WLXMDM , B.F_CODE as XH , B.F_CBBH as bz2 , B.F_CPBH as bz1 , B.F_YSBH as bz3
INTO ##temp_pzb3
FROM OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as
A
left join OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+
@tbnamelshspz +'] as B
on a.f_date =b.f_date and a.f_pzbh=b.F_pzbh and a.F_flbh=b.F_flbh and A.f_kmbh = b.F_kmbh
and A.f_code =b.F_code where A.F_JZFX=''D'' and A.F_DATE<='''+ @jstime +''' and A.F_DATE>='''+ @kstime +''' and b.f_je is not
null '
print (@sql)
exec (@sql)
set @sql=''
set @sql='insert into '
z3)
select '''+ @xmbh+''',pzrq,pzbh,pzlx,SFPZZS,zy,rtrim
(kmbh),jfje,dfje,jfsl,dfsl,JBR,SHR,ZGR,WLBMDM,XJLLMC,WLDWDM,WLGRDM,WLXMDM,XH,bz2,bz1,bz3 from ##temp_pzb3'
print (@sql)
exec (@sql)
drop table ##temp_pzb3
set @sql=''
set @sql='SELECT A.F_DATE as PZRQ,''统一编号'' + A.F_QTXX + ''内部编号'' + A.F_PZBH as PZBH ,A.F_PZLX as PZLX , A.F_FDZS as
SFPZZS ,
A.F_JY as ZY,A.F_KMBH as KMBH,0 as JFJE,A.F_JE as DFJE, 0 as JFSL , A.F_SL as DFSL , A.F_ZDXM as JBR,A.F_SHXM as SHR,
A.F_JZXM as ZGR , B.F_BMBH as WLBMDM , b.F_xjbh as XJLLMC , B.f_dw_bh as WLDWDM , B.F_zg_bh as WLGRDM , B.F_HSBH as WLXMDM ,
A.F_CODE as XH , B.F_CBBH as bz2 , B.F_CPBH as bz1 , B.F_YSBH as bz3
INTO ##temp_pzb4
FROM OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as
A
left join OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+
@tbnamelshspz +'] as B
on a.f_date =b.f_date and a.f_pzbh=b.F_pzbh and a.F_flbh=b.F_flbh and A.f_kmbh = b.F_kmbh
and A.f_code =b.F_code where A.F_JZFX=''D'' and A.F_DATE<='''+ @jstime +''' and A.F_DATE>='''+ @kstime +''' and b.f_je is
null '
print (@sql)
exec (@sql)
set @sql=''
set @sql='insert into '
z3)
select '''+ @xmbh+''',pzrq,pzbh,pzlx,SFPZZS,zy,rtrim
(kmbh),jfje,dfje,jfsl,dfsl,JBR,SHR,ZGR,WLBMDM,XJLLMC,WLDWDM,WLGRDM,WLXMDM,XH,bz2,bz1,bz3 from ##temp_pzb4'
print (@sql)
exec (@sql)
drop table ##temp_pzb4
--------------------------kmb
set @tbname1=''
set @tbname1='xt_kmb_' + @xmbh + @year
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tbname1+']')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @sql='delete from [dbo].[' + @tbname1 + ']'
print (@sql)
exec (@sql)
end
declare @tbnameackmje varchar(100)
declare @tbnamelskmzd varchar(100)
if @byear=1
begin
set @tbnameackmje='ackmje'+@year
set @tbnamelskmzd='lskmzd'+@year
end
else
begin
set @tbnameackmje='ackmje'
set @tbnamelskmzd='lskmzd'
end
set @sql=''
set @sql='select A.F_KMBH as KMBH, B.F_KMMC as KMMC
into ##tmp_zhkmb1
from ( select F_KMBH from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnameackmje +']
where F_snjz<>0 group by F_KMBH ) as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelskmzd +']
as B on a.F_kmbh=b.F_kmbh '
print (@sql)
exec (@sql)
set @sql='insert into ' +@tbname1+'(xmbh,kmbh,kmmc) select '''+ @xmbh+''',rtrim(kmbh),kmmc from ##tmp_zhkmb1'
print (@sql)
exec (@sql)
drop table ##tmp_zhkmb1
set @sql=''
set @sql='select A.F_KMBH as KMBH, B.F_KMMC as KMMC
into ##tmp_zhkmb2
from
( ( select F_KMBH from ( select F_KMBH , sum(F_snjz) as JE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnameackmje +']
group by F_KMBH ) as l where l.je=0) as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelskmzd +']
as B on a.F_kmbh=b.F_kmbh ) left join (select F_KMBH from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +']
where F_DATE<='''+ @jstime +''' and F_DATE>='''+ @kstime +'''
group by F_KMBH) as C on C.F_kmbh like A.F_kmbh+''%'' where C.F_kmbh is not null group by A.F_KMBH , B.F_KMMC '
print (@sql)
exec (@sql)
set @sql=''
set @sql='insert into ' +@tbname1+'(xmbh,kmbh,kmmc) select '''+ @xmbh+''',rtrim(kmbh),kmmc from ##tmp_zhkmb2'
print (@sql)
exec (@sql)
drop table ##tmp_zhkmb2
---yeb
set @tbname2=''
set @tbname2='xt_kmyeb_' + @xmbh + @year
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].['+@tbname2+']')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
set @sql='delete from [dbo].[' + @tbname2 + ']'
print (@sql)
exec (@sql)
end
set @sql='select A.F_CODE as KMLB , A.F_KMBH as KMBH, A.F_snjz as NCJFYE , 0 as NCDFYE , 0 as QMJFYE ,
0 as QMDFYE
into ##temp_zhyeb1 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnameackmje +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@tbname2+'(xmbh,kmbh,ncjfye,ncdfye) select '''+ @xmbh+''',rtrim(kmbh),ncjfye,ncdfye from
##temp_zhyeb1'
print (@sql)
exec (@sql)
drop table ##temp_zhyeb1
set @sql='select A.F_CODE as KMLB , A.F_KMBH as KMBH, 0 as NCJFYE , -A.F_snjz as NCDFYE , 0 as QMJFYE , 0 as QMDFYE
into ##temp_zhyeb2 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnameackmje +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@tbname2+'(xmbh,kmbh,ncjfye,ncdfye) select '''+ @xmbh+''',rtrim(kmbh),ncjfye,ncdfye from
##temp_zhyeb2'
print (@sql)
exec (@sql)
drop table ##temp_zhyeb2
set @sql='select A.F_CODE as KMLB , A.F_KMBH as KMBH, 0 as NCJFYE , -A.F_snjz as NCDFYE , 0 as QMJFYE , 0 as QMDFYE
into ##temp_zhyeb3 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnameackmje +']
as A where a.F_snjz=0 and (A.F_JFLJ<>0 or A.F_DFLJ<>0)'
print (@sql)
exec (@sql)
set @sql='insert into ' +@tbname2+'(xmbh,kmbh,ncjfye,ncdfye) select '''+ @xmbh+''',rtrim(kmbh),ncjfye,ncdfye from
##temp_zhyeb3'
print (@sql)
exec (@sql)
drop table ##temp_zhyeb3
set @sql='select d.F_CODE as KMLB , d.F_KMBH as KMBH, 0 as NCJFYE , 0 as NCDFYE , 0 as QMJFYE , 0 as QMDFYE
into ##temp_zhyeb4 from
( select c.F_KMBH ,c.F_CODE , sum(c.JF) as J , sum(c.DF) as D from ( select a.F_KMBH ,a.F_CODE , sum(a.F_JE) as JF , 0 as DF
from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as a
where a.F_JZFX=''J'' and a.F_DATE>='''+ @kstime +''' and a.F_DATE<='''+ @jstime +''' group by a.F_KMBH ,a.F_CODE union
select b.F_KMBH ,b.F_CODE , 0 as JF , sum(b.F_JE) as DF from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamepzk +'] as b
where b.F_JZFX=''D'' and b.F_DATE>='''+ @kstime +''' and b.F_DATE<='''+ @jstime +''' group by b.F_KMBH ,b.F_CODE ) as c
group by c.F_KMBH ,c.F_CODE ) as d where d.J=0 and d.D=0 '
print (@sql)
exec (@sql)
set @sql='insert into ' +@tbname2+'(xmbh,kmbh,ncjfye,ncdfye) select '''+ @xmbh+''',rtrim(kmbh),ncjfye,ncdfye from
##temp_zhyeb4'
print (@sql)
exec (@sql)
drop table ##temp_zhyeb4
--往来余额
declare @xt_wlk varchar(100)
set @xt_wlk='XT_LFZB_'+ @xmbh + @year
declare @wlk varchar(100)
declare @wlk1 varchar(100)
declare @wlk2 varchar(100)
declare @wlk3 varchar(100)
declare @wlk4 varchar(100)
declare @wlk5 varchar(100)
declare @wlk6 varchar(100)
declare @wlk7 varchar(100)
declare @wlk8 varchar(100)
if @byear=1
begin
set @wlk='acWlje' + @year
set @wlk1='lsZgje' + @year
set @wlk2='ACBMZD' + @year
set @wlk3='lszgzd' + @year
set @wlk4='lsbmje' + @year
set @wlk5='achsje' + @year
set @wlk6='acXJje' + @year
set @wlk7='acCPJE' + @year
set @wlk8='acCBJE' + @year
end
else
begin
set @wlk='acWlje'
set @wlk1='lsZgje'
set @wlk2='ACBMZD'
set @wlk3='lszgzd'
set @wlk4='lsbmje'
set @wlk5='achsje'
set @wlk6='acXJje'
set @wlk7='acCPJE'
set @wlk8='acCBJE'
end
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_DWBH as WLDWDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk1 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLDWDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLDWDM,ncjfye,ncdfye from ##t_wlk1'
print (@sql)
exec (@sql)
drop table ##t_wlk1
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_DWBH as WLDWDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk2 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLDWDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLDWDM,ncjfye,ncdfye from ##t_wlk2'
print (@sql)
exec (@sql)
drop table ##t_wlk2
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_DWBH AS WLDWDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk3 from (
select F_KMBH , F_DWBH,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk +']
where F_SNJZ=0 group by F_KMBH ,F_DWBH,F_CODE ) AS A LEFT JOIN ( select F_KMBH , F_DW_bh as F_DWBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_DW_BH) <>'''' group by F_KMBH ,F_DW_BH ,F_Code) AS B
ON A.F_Dwbh=B.F_DWbh AND A.F_KMBH=B.F_KMBH AND B.F_CODE like A.F_CODE+''%''
where B.F_Code is not null group by A.F_CODE , a.F_KMBH , a.F_DWBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLDWDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLDWDM,ncjfye,ncdfye from ##t_wlk3'
print (@sql)
exec (@sql)
drop table ##t_wlk3
set @sql='select B.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_ZGBH as WLGRDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk4 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk1 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk3 +']
as C where a.F_zgbh=c.F_Zgbh and B.F_BMBH= c.F_dwbh and a.f_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLGRDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLGRDM,ncjfye,ncdfye from ##t_wlk4'
print (@sql)
exec (@sql)
drop table ##t_wlk4
set @sql='select B.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_ZGBH as WLGRDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk5 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk1 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk3 +']
as C where a.F_zgbh=c.F_Zgbh and B.F_BMBH= c.F_dwbh and a.f_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLGRDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLGRDM,ncjfye,ncdfye from ##t_wlk5'
print (@sql)
exec (@sql)
drop table ##t_wlk5
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_ZGBH AS WLGRDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk6 from ( select B.F_CODE , A.F_KMBH , A.F_ZGBH from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk1 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk3 +']
as C where a.F_zgbh=c.F_Zgbh and B.F_BMBH= c.F_dwbh and a.f_snjz=0 group by B.F_CODE , A.F_KMBH , A.F_ZGBH ) AS A
LEFT JOIN ( select F_KMBH , F_ZG_BH as F_ZGBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_ZG_BH) <>'''' group by F_KMBH ,F_ZG_BH ,F_Code) AS B ON A.F_ZGbh=B.F_ZGbh AND
A.F_KMBH=B.F_KMBH AND B.F_CODE like A.F_CODE+''%'' where B.F_Code is not null group by A.F_CODE,A.F_KMBH,A.F_ZGBH '
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLGRDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLGRDM,ncjfye,ncdfye from ##t_wlk6'
print (@sql)
exec (@sql)
drop table ##t_wlk6
set @sql='select B.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_BMBH as WLBMDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk7 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk4 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B where a.f_bmbh=b.F_bmbh and a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLBMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLBMDM,ncjfye,ncdfye from ##t_wlk7'
print (@sql)
exec (@sql)
drop table ##t_wlk7
set @sql='select B.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_BMBH as WLBMDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk8 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk4 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B where a.f_bmbh=b.F_bmbh and a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLBMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLBMDM,ncjfye,ncdfye from ##t_wlk8'
print (@sql)
exec (@sql)
drop table ##t_wlk8
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_BMBH AS WLBMDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk9 from ( select B.F_CODE , A.F_KMBH , A.F_BMBH from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk4 +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk2 +']
as B where a.f_bmbh=b.F_bmbh and a.F_snjz=0 group by A.F_KMBH ,A.F_BMBH, B.F_CODE ) AS A LEFT JOIN (
select F_KMBH , F_BMBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_BMBH) <>'''' group by F_KMBH ,F_BMBH ,F_Code) AS B ON A.F_BMbh=B.F_BMbh
AND A.F_KMBH=B.F_KMBH AND B.F_CODE like A.F_CODE + ''%'' where B.F_Code is not null group by A.F_CODE , A.F_KMBH , A.F_BMBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLBMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLBMDM,ncjfye,ncdfye from ##t_wlk9'
print (@sql)
exec (@sql)
drop table ##t_wlk9
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_HSBH as WLXMDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk10 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk5 +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLXMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLXMDM,ncjfye,ncdfye from ##t_wlk10'
print (@sql)
exec (@sql)
drop table ##t_wlk10
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_HSBH as WLXMDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk11 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk5 +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLXMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLXMDM,ncjfye,ncdfye from ##t_wlk11'
print (@sql)
exec (@sql)
drop table ##t_wlk11
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_HSBH AS WLXMDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk12 from ( select F_KMBH , F_HSBH , F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk5 +']
where F_SNJZ=0 group by F_KMBH ,F_HSBH,F_CODE ) AS A LEFT JOIN ( select F_KMBH , F_HSBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_HSBH) <>'''' group by F_KMBH ,F_HSBH ,F_Code) AS B ON A.F_HSbh=B.F_HSbh AND A.F_KMBH=B.F_KMBH
AND B.F_CODE like A.F_CODE + ''%'' where B.F_Code is not null group by A.F_CODE , A.F_KMBH , A.F_HSBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,WLXMDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),WLXMDM,ncjfye,ncdfye from ##t_wlk12'
print (@sql)
exec (@sql)
drop table ##t_wlk12
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_XJBH as XJLLDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk13 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk6 +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,XJLLDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),XJLLDM,ncjfye,ncdfye from ##t_wlk13'
print (@sql)
exec (@sql)
drop table ##t_wlk13
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_XJBH as XJLLDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk14 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk6 +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,XJLLDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),XJLLDM,ncjfye,ncdfye from ##t_wlk14'
print (@sql)
exec (@sql)
drop table ##t_wlk14
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_XJBH AS XJLLDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk15 from ( select F_KMBH , F_XJBH , F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk6 +']
where F_SNJZ=0 group by F_KMBH ,F_XJBH,F_CODE ) AS A LEFT JOIN ( select F_KMBH , F_XJBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_XJBH) <>'''' group by F_KMBH ,F_XJBH ,F_Code) AS B ON A.F_XJbh=B.F_XJbh AND A.F_KMBH=B.F_KMBH
AND B.F_CODE like A.F_CODE + ''%'' where B.F_Code is not null group by A.F_CODE , A.F_KMBH , A.F_XJBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,XJLLDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),XJLLDM,ncjfye,ncdfye from ##t_wlk15'
print (@sql)
exec (@sql)
drop table ##t_wlk15
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_CPBH as CPZXDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk16 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk7 +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CPZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CPZXDM,ncjfye,ncdfye from ##t_wlk16'
print (@sql)
exec (@sql)
drop table ##t_wlk16
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_CPBH as CPZXDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk17 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk7 +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CPZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CPZXDM,ncjfye,ncdfye from ##t_wlk17'
print (@sql)
exec (@sql)
drop table ##t_wlk17
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_CPBH AS CPZXDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk18 FROM ( select F_KMBH , F_CPBH , F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk7 +']
where F_SNJZ=0 group by F_KMBH ,F_CPBH,F_CODE ) AS A LEFT JOIN ( select F_KMBH , F_CPBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_CPBH) <>'''' group by F_KMBH ,F_CPBH ,F_Code) AS B ON A.F_CPbh=B.F_CPbh AND A.F_KMBH=B.F_KMBH
AND B.F_CODE like A.F_CODE + ''%'' where B.F_Code is not null group by A.F_CODE , A.F_KMBH , A.F_CPBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CPZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CPZXDM,ncjfye,ncdfye from ##t_wlk18'
print (@sql)
exec (@sql)
drop table ##t_wlk18
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_CBBH as CBZXDM , A.F_snjz as NCJFYE , 0 as NCDFYE
into ##t_wlk19 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk8 +']
as A where a.F_snjz>0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CBZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CBZXDM,ncjfye,ncdfye from ##t_wlk19'
print (@sql)
exec (@sql)
drop table ##t_wlk19
set @sql='select A.F_CODE as ZRZX , A.F_KMBH as KMBH , A.F_CBBH as CBZXDM , 0 as NCJFYE , -A.F_snjz as NCDFYE
into ##t_wlk20 from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk8 +']
as A where a.F_snjz<0'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CBZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CBZXDM,ncjfye,ncdfye from ##t_wlk20'
print (@sql)
exec (@sql)
drop table ##t_wlk20
set @sql='SELECT A.F_CODE AS ZRZX , A.F_KMBH AS KMBH , A.F_CBBH AS CBZXDM , 0 as NCJFYE , 0 as NCDFYE
into ##t_wlk21 FROM ( select F_KMBH , F_CBBH , F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlk8 +']
where F_SNJZ=0 group by F_KMBH ,F_CBBH,F_CODE ) AS A LEFT JOIN ( select F_KMBH , F_CBBH ,F_CODE from
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @tbnamelshspz +']
where rtrim(F_CBBH) <>'''' group by F_KMBH ,F_CBBH ,F_Code) AS B ON A.F_CBbh=B.F_CBbh AND
A.F_KMBH=B.F_KMBH AND B.F_CODE like A.F_CODE + ''%'' where B.F_Code is not null group by A.F_CODE , A.F_KMBH , A.F_CBBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlk+'(xmbh,ZRZX,KMBH,CBZXDM,NCJFYE,NCDFYE) select '''+ @xmbh+''',ZRZX,rtrim
(kmbh),CBZXDM,ncjfye,ncdfye from ##t_wlk21'
print (@sql)
exec (@sql)
drop table ##t_wlk21
--单位往来
declare @xt_dwwl varchar(100)
set @xt_dwwl='XT_DWWL_'+ @xmbh + @year
declare @wldw varchar(100)
declare @wldw1 varchar(100)
if @byear=1
begin
set @wldw='lswldw' + @year
set @wldw1='lswlfl' + @year
end
else
begin
set @wldw='lswldw'
set @wldw1='lswlfl'
end
set @sql='select A.F_DWBH as WLDWDM , A.F_dwmc as WLDWMC , B.F_FLBH AS DQBH , B.F_FLMC AS DQMC
into ##t_dwwl FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wldw +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wldw1 +']
as B WHERE A.F_FLBH=B.F_FLBH'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_dwwl+'(xmbh,WLDWDM,WLDWMC,DQBH,DQMC) select '''+ @xmbh+''',WLDWDM,WLDWMC,DQBH,DQMC from
##t_dwwl'
print (@sql)
exec (@sql)
drop table ##t_dwwl
--个人往来
declare @xt_dwgr varchar(100)
set @xt_dwgr='XT_GRWL_'+ @xmbh + @year
declare @wlgr varchar(100)
if @byear=1
begin
set @wlgr='lsZgzd' + @year
end
else
begin
set @wlgr='lsZgzd'
end
set @sql='select F_ZGBH as WLGRDM , F_name as XM
into ##t_dwgr FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlgr +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_dwgr+'(xmbh,WLGRDM,XM) select '''+ @xmbh+''',WLGRDM,XM from ##t_dwgr'
print (@sql)
exec (@sql)
drop table ##t_dwgr
--部门往来
declare @xt_dwbm varchar(100)
set @xt_dwbm='XT_BMK_'+ @xmbh + @year
declare @wlbm varchar(100)
if @byear=1
begin
set @wlbm='acbmzd' + @year
end
else
begin
set @wlbm='acbmzd'
end
set @sql='select F_bmbh as WLBMDM , F_bmmc as WLBMMC
into ##t_dwbm FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlbm +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_dwbm+'(xmbh,WLBMDM,WLBMMC) select '''+ @xmbh+''',WLBMDM,WLBMMC from ##t_dwbm'
print (@sql)
exec (@sql)
drop table ##t_dwbm
--项目往来
declare @xt_wlxm varchar(100)
set @xt_wlxm='XT_XMK_'+ @xmbh + @year
declare @wlxm varchar(100)
declare @wlxm1 varchar(100)
if @byear=1
begin
set @wlxm='lshszd' + @year
set @wlxm1='lshsfl' + @year
end
else
begin
set @wlxm='lshszd'
set @wlxm1='lshsfl'
end
set @sql='select A.F_hsbh as WLXMDM , A.F_hsmc as WLXMMC , A.F_FLBH as BZ1 , B.F_FLMC as BZ2
into ##t_dwxm FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxm +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxm1 +']
as b where a.F_FLBH=B.F_FLBH
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxm+'(xmbh,WLXMDM,WLXMMC,bz1,bz2) select '''+ @xmbh+''',WLXMDM,WLXMMC,bz1,bz2 from ##t_dwxm'
print (@sql)
exec (@sql)
drop table ##t_dwxm
--项目余额
declare @xt_wlxmye varchar(100)
set @xt_wlxmye='XT_XMFP_'+ @xmbh + @year
declare @wlxmye varchar(100)
if @byear=1
begin
set @wlxmye='ACCBZX' + @year
end
else
begin
set @wlxmye='ACCBZX'
end
set @sql='select F_CBbh as xmdm , F_cbmc as xmmc , ''1'' as xmlx
into ##t_xmye FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxmye +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxmye+'(xmbh,xmdm,xmmc,xmlx) select '''+ @xmbh+''',xmdm,xmmc,xmlx from ##t_xmye'
print (@sql)
exec (@sql)
drop table ##t_xmye
declare @wlxmye1 varchar(100)
if @byear=1
begin
set @wlxmye1='LSCPZD' + @year
end
else
begin
set @wlxmye1='LSCPZD'
end
set @sql='select F_cpbh as xmdm , F_cpmc as xmmc , ''2'' as xmlx
into ##t_xmye1 FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxmye1 +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxmye+'(xmbh,xmdm,xmmc,xmlx) select '''+ @xmbh+''',xmdm,xmmc,xmlx from ##t_xmye1'
print (@sql)
exec (@sql)
drop table ##t_xmye1
declare @wlxmye2 varchar(100)
if @byear=1
begin
set @wlxmye2='ACXJLL' + @year
end
else
begin
set @wlxmye2='ACXJLL'
end
set @sql='select F_XJbh as xmdm , F_XJmc as xmmc , ''3'' as xmlx
into ##t_xmye2 FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxmye2 +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxmye+'(xmbh,xmdm,xmmc,xmlx) select '''+ @xmbh+''',xmdm,xmmc,xmlx from ##t_xmye2'
print (@sql)
exec (@sql)
drop table ##t_xmye2
declare @wlxmye3 varchar(100)
if @byear=1
begin
set @wlxmye3='ACZRZX' + @year
end
else
begin
set @wlxmye3='ACZRZX'
end
set @sql='select F_Code as xmdm , F_stmc as xmmc , ''4'' as xmlx
into ##t_xmye3 FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxmye3 +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxmye+'(xmbh,xmdm,xmmc,xmlx) select '''+ @xmbh+''',xmdm,xmmc,xmlx from ##t_xmye3'
print (@sql)
exec (@sql)
drop table ##t_xmye3
declare @wlxmye4 varchar(100)
if @byear=1
begin
set @wlxmye4='RPTLBZD' + @year
end
else
begin
set @wlxmye4='RPTLBZD'
end
set @sql='select lbzd_BH as xmdm , lbzd_MC as xmmc , ''5'' as xmlx
into ##t_xmye4 FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @wlxmye4 +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_wlxmye+'(xmbh,xmdm,xmmc,xmlx) select '''+ @xmbh+''',xmdm,xmmc,xmlx from ##t_xmye4'
print (@sql)
exec (@sql)
drop table ##t_xmye4
--报表单位
declare @xt_bbdw varchar(100)
set @xt_bbdw='XT_BBDW_'+ @xmbh + @year
declare @bb varchar(100)
declare @bb1 varchar(100)
declare @bb2 varchar(100)
if @byear=1
begin
set @bb='RPTBBZD' + @year
set @bb1='RPTDWSB' + @year
set @bb2='RPTLBZD' + @year
end
else
begin
set @bb='RPTBBZD'
set @bb1='RPTDWSB'
set @bb2='RPTLBZD'
end
set @sql='select A.DWZD_BH as ZRZX , A.BBZD_BH as BBBH , B.BBZD_DATE as BBSJ , B.BBZD_MC as BBMC
into ##t_bb FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bb +']
as B ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bb1 +']
as A where A.BBZD_BH=B.BBZD_BH
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbdw+'(xmbh,ZRZX,BBBH,BBSJ,BBMC) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,BBMC from ##t_bb'
print (@sql)
exec (@sql)
drop table ##t_bb
set @sql='select B.lbzd_bh as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , A.BBZD_MC as BBMC
into ##t_bb1 FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bb +']
as A ,
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bb2 +']
as B
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbdw+'(xmbh,ZRZX,BBBH,BBSJ,BBMC) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,BBMC from ##t_bb1'
print (@sql)
exec (@sql)
drop table ##t_bb1
--报表公式
declare @xt_bbgs varchar(100)
set @xt_bbgs='XT_BBGS_'+ @xmbh + @year
declare @bbgs varchar(100)
if @byear=1
begin
set @bbgs='RPTBBGS' + @year
end
else
begin
set @bbgs='RPTBBGS'
end
set @sql='select BBZD_BH as BBBH , BBZD_DATE as BBSJ , BBZD_GS as BBNR
into ##t_bbgs FROM
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbgs +']
'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbgs+'(BBBH,BBSJ,BBNR) select BBBH,BBSJ,BBNR from ##t_bbgs'
print (@sql)
exec (@sql)
drop table ##t_bbgs
--报表数据
declare @xt_bbsj varchar(100)
set @xt_bbsj='XT_BBSJ_'+ @xmbh + @year
declare @bbsj varchar(100)
declare @bbsj1 varchar(100)
declare @bbsj2 varchar(100)
declare @bbsj3 varchar(100)
if @byear=1
begin
set @bbsj='RPTHZSJ' + @year
set @bbsj1='RPTHZD' + @year
set @bbsj2='RPTLZD' + @year
set @bbsj3='RPTDWSJ' + @year
end
else
begin
set @bbsj='RPTHZSJ'
set @bbsj1='RPTHZD'
set @bbsj2='RPTLZD'
set @bbsj3='RPTDWSJ'
end
set @sql='select A.LBZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(B.hzd_zb,0) as HZD ,
isnull(C.lzd_zb,0) as LZD , A.DYZD_data as SJ
into ##t_bbsj from (
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date where B.hzd_zb is not null
and C.lzd_zb is not null and A.DYZD_SJ is null'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj'
print (@sql)
exec (@sql)
drop table ##t_bbsj
set @sql='select A.LBZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(B.hzd_zb,0) as HZD , isnull(C.lzd_zb,0)
as LZD ,
A.DYZD_SJ as SJ
into ##t_bbsj1 from
(
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date
where B.hzd_zb is not null and C.lzd_zb is not null and A.DYZD_SJ is not null'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj1'
print (@sql)
exec (@sql)
drop table ##t_bbsj1
set @sql='select A.LBZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(A.hzd_orde,0) as HZD , isnull
(A.lzd_orde,0) as LZD ,
A.DYZD_data as SJ
into ##t_bbsj2 from
(
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date
where B.hzd_zb is null or C.lzd_zb is null and A.DYZD_SJ is null'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj2'
print (@sql)
exec (@sql)
drop table ##t_bbsj2
set @sql='select A.LBZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(A.hzd_orde,0) as HZD , isnull
(A.lzd_orde,0) as LZD ,
A.DYZD_SJ as SJ
into ##t_bbsj3 from
(
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date
where B.hzd_zb is null or C.lzd_zb is null and A.DYZD_SJ is not null'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj3'
print (@sql)
exec (@sql)
drop table ##t_bbsj3
set @sql='select A.DWZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(A.hzd_orde,0) as HZD , isnull
(A.lzd_orde,0) as LZD ,
A.DYZD_SJ as SJ
into ##t_bbsj4 from
(
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj3 +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date where B.hzd_zb is null or C.lzd_zb is
null'
print (@sql)
exec (@sql)
delete from ##t_bbsj4 where lzd='null'
delete from ##t_bbsj4 where HZD='null'
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj4 where lzd<>''null'' or HZD<>''null'''
print (@sql)
exec (@sql)
drop table ##t_bbsj4
set @sql='select A.DWZD_BH as ZRZX , A.BBZD_BH as BBBH , A.BBZD_DATE as BBSJ , isnull(B.hzd_zb,0) as HZD , isnull(C.lzd_zb,0)
as LZD ,
A.DYZD_SJ as SJ
into ##t_bbsj5 from
(
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj3 +']
as A left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj1 +']
as B on A.hzd_orde=B.hzd_orde and A.BBZD_BH=B.BBZD_BH and A.BBZD_DATE=B.BBZD_date ) left join
OPENDATASOURCE (''Microsoft.Jet.OLEDB.4.0'',''Data Source='+ @path+';User ID=Admin;Password='' )...['+ @bbsj2 +']
as C on A.lzd_orde=c.lzd_orde and A.BBZD_BH=C.BBZD_BH and A.BBZD_DATE=C.BBZD_date
where B.hzd_zb is not null and C.lzd_zb is not null'
print (@sql)
exec (@sql)
set @sql='insert into ' +@xt_bbsj+'(xmbh,ZRZX,BBBH,BBSJ,HZD,LZD,SJ) select '''+ @xmbh+''',ZRZX,BBBH,BBSJ,isnull
(HZD,0),isnull(LZD,0),SJ from ##t_bbsj5 '
print (@sql)
exec (@sql)
drop table ##t_bbsj5
--
----科目长度值
delete from xt_sjkmcdz where xmbh=@xmbh
set @sql='insert into xt_sjkmcdz(xmbh,kmcdz) select distinct '''+@xmbh+''' as xmbh, len(rtrim(kmbh)) from '+ @tbname1
print (@sql)
exec (@sql)
---修改科目名称
declare @tint tinyint
declare @newcol int
declare @oldval int --记录上一次的值
declare @curval int --当前游标的值
set @tint=1 --记录当前循环中游标的位置
declare newcur cursor local for
select distinct kmcdz from xt_sjkmcdz where xmbh=@xmbh
open newcur
fetch next from newcur into @newcol
while @@fetch_status =0
begin
if @tint=1 begin
set @oldval= @newcol --如果是第一条则将值记录到@oldval
end
else begin
--set @sql='update a set a.kmmc=b.kmmc +''-'' +a.kmmc from (select * from '+ @tbname1 +' where len
(kmbh)=' + convert(varchar(2),@newcol) + ' and kmmc not like ''%-%'') as a ,
--(select * from '+ @tbname1 +' where len(kmbh)=' + convert(varchar(2),@oldval) + ') as b where a.kmbh like
b.kmbh+''%'''
set @sql='update a set a.kmmc=rtrim(b.kmmc) +''-'' +rtrim(a.kmmc) from '+ @tbname1 +' as a,'+
@tbname1 +' as b
where len(rtrim(a.kmbh))=' + convert(varchar(2),@newcol) + ' and a.kmmc not like ''%-%'' and len(rtrim(b.kmbh))=' + convert
(varchar(2),@oldval) + ' and a.kmbh like b.kmbh+''%'''
exec (@sql)
set @oldval = @newcol --如果
end
set @tint = @tint + 1
print @sql
print @oldval
print @newcol
fetch next from newcur into @newcol
end
close newcur
deallocate newcur
---名称一体化
set @sql='update a set a.kmmc=b.kmmc from '+ @tbname +' as a,' + @tbname1 + ' as b where a.kmbh=b.kmbh'
print (@sql)
exec (@sql)
set @sql='update a set a.kmmc=b.kmmc from '+ @tbname2 +' as a,' + @tbname1 + ' as b where a.kmbh=b.kmbh'
print (@sql)
exec (@sql)
/*---总帐余额汇总
delete from SZ_MBB where mbjc='AAA'
insert into SZ_MBB (mbjc,sfhz) values('AAA','1')
delete from SZ_MYSZB where xmbh=@xmbh
insert into SZ_MYSZB (xmbh,yhbh,mbjc) values(@xmbh,'N','AAA')
*/
-- 设置转数标志
declare @txmbh varchar(10)
set @txmbh=right(@xmbh,3)
update system.dbo.xt_xmb set zswc='1' where xmbh=@txmbh
--exec sjzh_lc6all '100000101','20050101','20050231','H:\dq\二处.Mdb'
--select distinct '101100101' as xmbh, len(kmbh) from xt_kmb_1011001012004
--select * from xt_sjkmcdz
--update system.dbo.xt_xmb set zswc='1' where fdwbh='100000'
--select * from system.dbo.xt_xmb
--select * from xt_pzb_1000001012005
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
