应收应付余额表(查询语句)

/*

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'cx..apye') AND type in (N'U'))
DROP TABLE cx..apye
Create Table cx..apye (cDwCode nvarchar(20),cDwName nvarchar(120),cDeptCode nvarchar(20),cDepName nvarchar(120),cPerson nvarchar(20), cPersonName nvarchar(120), cInvCode nvarchar(20), cInvName nvarchar(120),cInvStd nvarchar(60),cDwCCode nvarchar(20),cDWCName nvarchar(120),cDwDCode nvarchar(20),cDCName nvarchar(120),cHDwCode nvarchar(20),cHDwName nvarchar(120),cHDptCode nvarchar(20),cHDepName nvarchar(120),cHPsnCode nvarchar(20),cHPersonName nvarchar(120),cInvCCode nvarchar(20),cInvCName nvarchar(120),cCode nvarchar(20),cCode_Name nvarchar(120),cItem_Class nvarchar(2),cItem_Name nvarchar(20),cItemCode nvarchar(20),cItemName nvarchar(60),cContractType nvarchar(12),cContractTypeName nvarchar(40),cContractID nvarchar(64),cContractName nvarchar(400),cCusCreditCompany nvarchar(20),cCusCreditName nvarchar(120),qc_f money,qc_s float,qc money,jf_f money,jf_s float,jf money,df_f money,df_s float,df money,jf_f2 money,jf_s2 float,jf2 money,df_f2 money,df_s2 float,df2 money,ye_f money,ye_s float,ye money,turnrate_f float,turnrate float,turndays_f float,turndays float,csysid nvarchar(2))




declare @rq as date,@rq2 as date
set @rq='2021-10-01'
set @rq2='2021-10-31'
truncate table apye
insert into cx..apye(cdwcode,cdwname,chdwcode,chdwname,ccode,ccode_name,qc_f,qc_s,qc,jf_f,jf_s,jf,df_f,df_s,df,csysid)
Select
max(cdwcode),max(cdwname) as cdwname,max(chdwcode),
max(case when vendor_1.cvenname<>'' then vendor_1.cvenname else vendor_1.cvenabbname end),
max(a.ccode),max(code.ccode_name),
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) and a.cexch_name<>'人民币' then iCAmount_f-iDAmount_f else 0 end) as qc_f,
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount_s-iDAmount_s else 0 end) as qc_s,
SUM(case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount-iDAmount else 0 end) as qc,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) and a.cexch_name<>'人民币'
then iCAmount_f else 0 end) as jf_f,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount_s else 0 end)
as jf_s,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount else 0 end) as jf,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) and a.cexch_name<>'人民币'
then iDAmount_f else 0 end) as df_f,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount_s else 0 end)
as df_s,
SUM(case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount else 0 end)
as df,
'AP' From Ap_DetailVend_s a with (nolock)
LEFT JOIN Vendor as Vendor_1 with (nolock) ON a.chdwcode=Vendor_1.cVenCode
LEFT JOIN code with (nolock) ON a.cCode=code.cCode where ((isnull(dcreditstart,dregdate)<=@rq2)) and a.iflag<=2
Group by cdwcode,chdwcode

truncate table cx..apye2
Insert into cx..apye2(cdwcode,cdwname,chdwcode,chdwname,ccode,ccode_name,qc_f,qc_s,qc,jf_f,jf_s,jf,df_f,df_s,df,jf_f2,jf_s2,jf2,df_f2,df_s2,df2)
Select max(cdwcode),max(cdwname) as cdwname,max(chdwcode),max(chdwname),max(a.ccode),max(ccode_name),sum(qc_f),sum(qc_s),sum(qc),sum(jf_f),sum(jf_s),sum(jf),sum(df_f),sum(df_s),sum(df),sum(jf_f2),sum(jf_s2),sum(jf2),sum(df_f2),sum(df_s2),sum(df2) From cx..apye a Group by cdwcode,chdwcode

update cx..apye2 set jf_f=(case when jf_f is null then 0 else jf_f end),jf_s=(case when jf_s is null then 0 else jf_s end),jf=(case when jf is null then 0 else jf end),df_f=(case when df_f is null then 0 else df_f end),df_s=(case when df_s is null then 0 else df_s end),df=(case when df is null then 0 else df end),jf_f2=(case when jf_f2 is null then 0 else jf_f2 end),jf_s2=(case when jf_s2 is null then 0 else jf_s2 end),jf2=(case when jf2 is null then 0 else jf2 end),df_f2=(case when df_f2 is null then 0 else df_f2 end),df_s2=(case when df_s2 is null then 0 else df_s2 end),df2=(case when df2 is null then 0 else df2 end)

Update cx..apye2 set ye_f=qc_f+jf_f-df_f-jf_f2+df_f2,ye=qc+jf-df-jf2+df2

Delete cx..apye2 where qc=0 And jf=0 And df=0 And jf2=0 And df2=0

Update cx..apye2 set turnrate_f=round((case when (qc_f+ye_f)=0 then 0 else (jf_f+df_f2)/((qc_f+ye_f)/2) end),2),
turnrate=round((case when qc+ye=0 then 0 else (jf+df2)/((qc+ye)/2) end),2)

Update cx..apye2 set turndays_f=(case when isnull(turnrate_f,0)=0 then 0 else (11-11+1)*30/turnrate_f end),turndays=(case when isnull(turnrate,0)=0 then 0 else (11-11+1)*30/turnrate end)

--数据保存在apye2
--数据另存为apye3

DROP TABLE cx..apye3

SELECT [cDwCode],[cDwName] as [cDwName],[cDeptCode] as [cDeptCode],[cDepName] as [cDepName],[cPerson] as [cPerson],[cPersonName] as [cPersonName],[cInvCode] as [cInvCode],[cInvName] as [cInvName],[cInvStd] as [cInvStd],[cDwCCode] as [cDwCCode],[cDWCName] as [cDWCName],[cDwDCode] as [cDwDCode],[cDCName] as [cDCName],[cHDwCode],[cHDwName] as [cHDwName],[cHDptCode] as [cHDptCode],[cHDepName] as [cHDepName],[cHPsnCode] as [cHPsnCode],[cHPersonName] as [cHPersonName],[cInvCCode] as [cInvCCode],[cInvCName] as [cInvCName],[cCode] as [cCode],[cCode_Name] as [cCode_Name],[cItem_Class] as [cItem_Class],[cItem_Name] as [cItem_Name],[cItemCode] as [cItemCode],[cItemName] as [cItemName],[cContractType] as [cContractType],[cContractTypeName] as [cContractTypeName],[cContractID] as [cContractID],[cContractName] as [cContractName],[cCusCreditCompany] as [cCusCreditCompany],[cCusCreditName] as [cCusCreditName],round([qc_s],2) as [qc_s],round([qc_f],2) as [qc_f],round([qc],2) as [qc],round([jf_s],2) as [jf_s],round([jf_f],2) as [jf_f],round([jf],2) as [jf],round([df_s],2) as [df_s],round([df_f],2) as [df_f],round([df],2) as [df],round([jf_s2],2) as [jf_s2],round([jf_f2],2) as [jf_f2],round([jf2],2) as [jf2],round([df_s2],2) as [df_s2],round([df_f2],2) as [df_f2],round([df2],2) as [df2],round([ye_s],2) as [ye_s],round([ye_f],2) as [ye_f],round([ye],2) as [ye],round([turnrate_f],2) as [turnrate_f],round([turnrate],2) as [turnrate],round([turndays_f],2) as [turndays_f],round([turndays],2) as [turndays],[csysid]
Into cx..apye3
from cx..apye2




DROP TABLE cx..apye4

SELECT [cDwCode],max([cDwName]) as [cDwName],max([cDeptCode]) as [cDeptCode],max([cDepName]) as [cDepName],max([cPerson]) as [cPerson],max([cPersonName]) as [cPersonName],max([cInvCode]) as [cInvCode],max([cInvName]) as [cInvName],max([cInvStd]) as [cInvStd],max([cDwCCode]) as [cDwCCode],max([cDWCName]) as [cDWCName],max([cDwDCode]) as [cDwDCode],max([cDCName]) as [cDCName],[cHDwCode],max([cHDwName]) as [cHDwName],max([cHDptCode]) as [cHDptCode],max([cHDepName]) as [cHDepName],max([cHPsnCode]) as [cHPsnCode],max([cHPersonName]) as [cHPersonName],max([cInvCCode]) as [cInvCCode],max([cInvCName]) as [cInvCName],max([cCode]) as [cCode],max([cCode_Name]) as [cCode_Name],max([cItem_Class]) as [cItem_Class],max([cItem_Name]) as [cItem_Name],max([cItemCode]) as [cItemCode],max([cItemName]) as [cItemName],max([cContractType]) as [cContractType],max([cContractTypeName]) as [cContractTypeName],max([cContractID]) as [cContractID],max([cContractName]) as [cContractName],max([cCusCreditCompany]) as [cCusCreditCompany],max([cCusCreditName]) as [cCusCreditName],sum(round([qc_s],2)) as [qc_s],sum(round([qc_f],2)) as [qc_f],sum(round([qc],2)) as [qc],sum(round([jf_s],2)) as [jf_s],sum(round([jf_f],2)) as [jf_f],sum(round([jf],2)) as [jf],sum(round([df_s],2)) as [df_s],sum(round([df_f],2)) as [df_f],sum(round([df],2)) as [df],sum(round([jf_s2],2)) as [jf_s2],sum(round([jf_f2],2)) as [jf_f2],sum(round([jf2],2)) as [jf2],sum(round([df_s2],2)) as [df_s2],sum(round([df_f2],2)) as [df_f2],sum(round([df2],2)) as [df2],sum(round([ye_s],2)) as [ye_s],sum(round([ye_f],2)) as [ye_f],sum(round([ye],2)) as [ye],sum(round([turnrate_f],2)) as [turnrate_f],sum(round([turnrate],2)) as [turnrate],sum(round([turndays_f],2)) as [turndays_f],sum(round([turndays],2)) as [turndays] Into cx..apye4 FROM cx..apye3 GROUP BY [cDwCode], [cHDwCode]

alter table cx..apye4 add zdfkje money
*/

/*

--应付明细 计算过程 无用
declare @rq as date,@rq2 as date
set @rq='2021-10-01'
set @rq2='2021-10-31'
Select
dvouchdate,cdigest,ctypename as '单据类型',cprocstyle as '处理方式',ccancelno,
case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount-iDAmount else 0 end as qc,
case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount else 0 end as jf,
case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount else 0 end
as df,
'AP' From Ap_DetailVend_s a with (nolock)
LEFT JOIN Vendor as Vendor_1 with (nolock) ON a.chdwcode=Vendor_1.cVenCode
LEFT JOIN code with (nolock) ON a.cCode=code.cCode
left join Ap_VouchType apc on apc.ctypecode=a.cvouchtype
where ((isnull(dcreditstart,dregdate)<=@rq2)) and a.iflag<=2 and cDWCode='09014006' and cprocstyle<>'9p' order by cprocstyle

*/




/*

DROP TABLE cx..apye5

select cdwcode,cdwname,vd.cvendefine6,qc,jf,df,ye,vd.iVenCreLine,iVenCreDate into cx..apye5 from cx..apye4 apye inner join vendor vd on apye.cdwcode=vd.cvencode
where isnull(cvendefine6,'')<>'' and ye>0
--没有账期 没有信用额度
and ISNULL(iVenCreLine,0)=0 and ISNULL(iVenCreDate,0)=0

DROP TABLE cx..apye6
select * into cx..apye6 from cx..apye5 where 1=2

insert into cx..apye6 (cdwcode,iVenCreLine,iVenCreDate)
select cdwcode,vd.iVenCreLine,iVenCreDate from cx..apye4 apye inner join vendor vd on apye.cdwcode=vd.cvencode
where isnull(cvendefine6,'')<>'' and ye>0
--有账期 没有信用额度
and ISNULL(iVenCreLine,0)=0 and ISNULL(iVenCreDate,0)<>0
union
select cdwcode,vd.iVenCreLine,iVenCreDate from cx..apye4 apye inner join vendor vd on apye.cdwcode=vd.cvencode
where isnull(cvendefine6,'')<>'' and ye>0
--没有账期 有信用额度
and ISNULL(iVenCreLine,0)<>0 and ISNULL(iVenCreDate,0)=0
union
select cdwcode,vd.iVenCreLine,iVenCreDate from cx..apye4 apye inner join vendor vd on apye.cdwcode=vd.cvencode
where isnull(cvendefine6,'')<>'' and ye>0
--有账期 有信用额度
and ISNULL(iVenCreLine,0)<>0 and ISNULL(iVenCreDate,0)<>0

alter table cx..apye6 add fkje money

--更新付款总金额

--更新付款总金额
update cx..apye6 set fkje=a.fkzje from cx..apye6 ap left join (
select cdwcode,abs(isnull(qc,0)+isnull(jf,0)-isnull(df,0)) as fkzje from
(
Select cdwcode,
sum( case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount-iDAmount else 0 end ) as qc,
sum( case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount else 0 end ) as jf,
sum( case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount else 0 end ) as df
From Ap_DetailVend_s a with (nolock)
LEFT JOIN Vendor as Vendor_1 with (nolock) ON a.chdwcode=Vendor_1.cVenCode
LEFT JOIN code with (nolock) ON a.cCode=code.cCode
left join Ap_VouchType apc on apc.ctypecode=a.cvouchtype
where ((isnull(dcreditstart,dregdate)<=@rq2)) and a.iflag<=2 and (cprocstyle='49' or cprocstyle='48' or cprocstyle='50' ) group by cdwcode ) as a) as a on a.cdwcode=ap.cdwcode

--信用额度视同于预付款在付款总金额增加信用额度

update cx..apye6 set fkje=fkje+ivencreline where ivencreline<>0







--建立apye7用来计算 付款单明细 对应付款金额

DROP TABLE cx..apye7
Select cdwcode,cvouchid,dvouchdate,
case when ((isnull(dcreditstart,dregdate)<@rq)) then iCAmount-iDAmount else 0 end as qc,
case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iCAmount else 0 end as jf,
case when ((isnull(dCreditStart,dregdate)>=@rq and isnull(dCreditStart,dregdate)<=@rq2)) then iDAmount else 0 end as df
into cx..apye7
From Ap_DetailVend_s a with (nolock)
LEFT JOIN Vendor as Vendor_1 with (nolock) ON a.chdwcode=Vendor_1.cVenCode
LEFT JOIN code with (nolock) ON a.cCode=code.cCode
left join Ap_VouchType apc on apc.ctypecode=a.cvouchtype
where ((isnull(dcreditstart,dregdate)<=@rq2)) and a.iflag<=2 and cDWCode in (select cdwcode from cx..apye6 group by cdwcode ) and
(cprocstyle='p0' or cprocstyle='BZ') order by cdwcode,dvouchdate

--增加付款（核销列)
alter table cx..apye7 add fkje money
alter table cx..apye7 add qmje money
alter table cx..apye7 add qmje2 money
alter table cx..apye7 add ivencreline money
alter table cx..apye7 add iVenCreDate int
--游标计算

update cx..apye7 set fkje=0,qmje=0
--游标计算
declare @ljfkje as money,@ljyf as money,@cdwcode varchar(500),@zfkje money,@djh varchar(500)
set @ljfkje=0
set @ljyf=0




DECLARE My_Cursor CURSOR --定义游标
FOR (
select cdwcode,fkje from cx..apye6
) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @cdwcode,@zfkje ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
--对比付款单 是否比付款金额小 如果小保留数据 否则删除数据
begin

DECLARE js CURSOR --定义游标
FOR(
select cvouchid,isnull(qc,0)+isnull(jf,0)-isnull(df,0) from cx..apye7 where cdwcode=@cdwcode
)
OPEN js; --打开游标
FETCH NEXT FROM js INTO @djh,@ljyf ; --读取第一行数据
WHILE @@FETCH_STATUS = 0
BEGIN

update cx..apye7 set fkje=@zfkje,qmje=@ljyf-@zfkje where cvouchid=@djh and cdwcode=@cdwcode
set @zfkje=@zfkje-@ljyf

/*
update cx..apye7 set fkje=@zfkje-@ljyf where cvouchid=@djh and cdwcode=@cdwcode
set @zfkje=@zfkje-@ljyf
*/

FETCH NEXT FROM js INTO @djh,@ljyf; --读取下一行数据
END
CLOSE js; --关闭游标
DEALLOCATE js; --释放游标

FETCH NEXT FROM My_Cursor INTO @cdwcode,@zfkje; --读取下一行数据
END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标



















*/




/*

update cx..apye7 set qmje2= jc from cx..apye7 ap
left join (select cdwcode,sum(qc+jf-df) as jc from cx..apye group by cdwcode ) as ay
on ap.cdwcode=ay.cdwcode
delete from cx..apye7 where isnull(qmje,0)<=0

update cx..apye7 set df=qc+jf-fkje where qmje<>qmje2 and fkje>=0
update cx..apye7 set df=qc+jf where qmje<>qmje2 and fkje<0
update cx..apye7 set df=qc+jf where qmje=qmje2
update cx..apye7 set ivencreline=ay6.ivencreline,iVenCreDate=ay6.iVenCreDate from cx..apye7 ay7 left join cx..apye6 ay6 on ay6.cdwcode=ay7.cdwcode
--
insert into cx..apye5(cdwcode,ye)
select cdwcode,sum(df) from cx..apye7 where iVenCreDate <>0 and cast(dvouchdate+ivencredate as date) <=@rq2 group by cdwcode

update cx..apye5 set cdwname=cvenname,cvendefine6=v.cvendefine6 from cx..apye5 a inner join vendor v on a.cdwcode=v.cvencode where isnull(cdwname,'')=''

update cx..apye4 set zdfkje=b.ye from cx..apye4 a inner join cx..apye5 b on a.cdwcode=b.cdwcode
*/