ccyt_CP_fx_new

create procedure ccyt_CP_fx_new      
(      
@xmbh varchar(20),--      
@zrzx varchar(60),      
@yf varchar(10),      
@kmbhstr varchar(50),      
@kmbhstrd varchar(50),      
@fzbh varchar(50),      
@d_jf  varchar(50),      
@d_df varchar(50),
@isall int ,
@hslx int     
)      
as      
begin       
declare @stryear varchar(20)      
set @stryear=substring(rtrim(ltrim(@yf)),1,4)      
declare @strxmbh varchar(20)      
set @strxmbh=rtrim(ltrim(@xmbh)) + @stryear
declare @sql  varchar(8000)

--dw
if @hslx =1
begin
 set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end

--gr
if @hslx =2
begin
 set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end

--bm
if @hslx =3
begin
 set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end

--xm
if @hslx =4
begin
 set @sql ='SELECT WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD INTO #DHG_XMLB FROM XT_DWWL_'+@strxmbh
end

--xj
if @hslx =5
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''3'' '
end

--cp
if @hslx =6
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb  from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''2'' '
end

--cb
if @hslx =7
begin
set @sql='select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_hslb from xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm like''' + ltrim(rtrim(@fzbh))+'%'' and xmlx=''1'' '
end

select @sql

set @sql='select a.xmdm as ''辅助代码'',a.xmmc as ''辅助名称'',b.df as ''收入''      
,(case when      
 (select sum(y.'+ ltrim(rtrim(@d_df))+') from xt_fzb_'+ ltrim(@strxmbh)+' as y where       
 y.kmbh like''' + rtrim(ltrim(@kmbhstrd)) + '%''      
 and y.zrzx=''' +ltrim(@zrzx)  +'''        
 and y.yf=''' + ltrim(@yf) + '''      
 and y.cpzxdm in' + ltrim(rtrim(@fzbh))+') =0       
 then 0 else convert(decimal(9,3),100*b.df/(select sum(m.'+ ltrim(rtrim(@d_df))+') from xt_fzb_'+ ltrim(@strxmbh)+' as m where       
 m.kmbh like''' + rtrim(ltrim(@kmbhstrd)) + ' %''     
 and m.zrzx=''' +ltrim(@zrzx)  +'''        
 and m.yf=''' + ltrim(@yf) + '''      
 and m.cpzxdm in' + ltrim(rtrim(@fzbh)) + '))      
 end      
) as ''收入比例(%)'' ,      
a.jf as ''成本'',(      
 case       
 when      
  (select sum(z.'+ ltrim(@d_jf)+') from xt_fzb_'+ ltrim(@strxmbh)+' as z where       
  z.kmbh like''' + rtrim(ltrim(@kmbhstr)) + '%''      
  and z.zrzx=''' +ltrim(@zrzx)  +'''       
  and z.yf=''' + ltrim(@yf) + '''      
  and z.cpzxdm in' + ltrim(rtrim(@fzbh))+') =0       
  then 0       
  else      
 convert(decimal(9,3),100*a.jf/(select sum(x.'+ ltrim(@d_jf)+') from xt_fzb_'+ ltrim(@strxmbh)+' as x where       
 x.kmbh like''' + rtrim(ltrim(@kmbhstr)) + '%''      
 and x.zrzx=''' +ltrim(@zrzx)  +'''       
 and x.yf=''' + ltrim(@yf) + '''      
 and x.cpzxdm in' + ltrim(rtrim(@fzbh))+'))       
 end      
 )      
 as ''成本比例(%)''       
,(b.df-a.jf) as ''差额''       
 from (    
select sum('+ ltrim(rtrim(@d_jf))+') as jf,xmdm,xmmc         
from (select xmdm,xmmc from         
 xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm in' + ltrim(rtrim(@fzbh))+'    
 and xmlx=''2'')  as c     
 left join     
 (select '+ ltrim(rtrim(@d_jf))+',cpzxdm     
from xt_fzb_'+ ltrim(@strxmbh)+'     
where     
kmbh like''' + rtrim(ltrim(@kmbhstr)) + ' %''    
and zrzx=''' +ltrim(@zrzx)  +'''         
 and yf=''' + ltrim(@yf) + '''      
 ) as d     
 on c.xmdm=d.cpzxdm     
 group by xmdm,xmmc    
) as a,    
(    
select sum('+ ltrim(rtrim(@d_df))+') as df,xmdm,xmmc         
from (select xmdm,xmmc from         
 xt_xmfp_'+ ltrim(@strxmbh)+' where xmdm in' + ltrim(rtrim(@fzbh))+'    
 and xmlx=''2'')  as c     
 left join     
 (select '+ ltrim(rtrim(@d_df))+',cpzxdm     
from xt_fzb_'+ ltrim(@strxmbh)+'     
where     
kmbh like''' + rtrim(ltrim(@kmbhstrd)) + ' %''    
and zrzx=''' +ltrim(@zrzx)  +'''         
 and yf=''' + ltrim(@yf) + '''      
 ) as d     
 on c.xmdm=d.cpzxdm     
 group by xmdm,xmmc    
)    
 as b    
 where a.xmdm=b.xmdm '
select @sql       
 set @sql=ltrim(rtrim(@sql))      
-- exec(@sql)      
end    
/*    
drop procedure ccyt_CP_fx_new
exec ccyt_CP_fx_new  '200083106',    
'21011324',    
'200501',    
'5401','51010001',    
'125009'    
,'BNJFFS','BNDFFS' ,0,6

select * from xt_dwwl_2000831062005
select * from xt_bmk_2000831062005
select * from xt_grwl_2000831062005
select * from xt_xmk_2000831062005
select * from xt_xmfp_2000831062005 where xmlx='2' --cp
select * from xt_xmfp_2000831062005 where xmlx='3' --xj
select * from xt_xmfp_2000831062005 where xmlx='1' --cb

select distinct dqbh as txmdm,dqbh as xmdm,dqmc as xmmc,LEN(LTRIM(RTRIM(dqbh))) as xmcd ,'0' as mxbz from xt_dwwl_2000831062005  union all
SELECT (dqbh +WLDWDM) as txmdm, WLDWDM AS XMDM,WLDWMC AS XMMC,LEN(LTRIM(RTRIM(WLDWDM ))) AS XMCD,'1' as mxbz FROM xt_dwwl_2000831062005

SELECT DISTINCT LEN(LTRIM(RTRIM(WLDWDM ))) FROM xt_dwwl_2000831062005
select xmdm,xmmc,LEN(LTRIM(RTRIM(xmdm))) AS XMCD into #dhg_xmlb from xt_xmfp_2000831062005 where xmdm like'125%' and xmlx='2'
*/