USE [qylyk]
GO

/****** Object:  StoredProcedure [dbo].[PI_ZYCX]    Script Date: 11/09/2022 09:18:02 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

--exec [PI_ZYCX] '1070004'
ALTER proc [dbo].[PI_ZYCX]            
@CardCode varchar(20)           
  
           
as            
  
------------------------------------------------------------------------   
     
  
------------------------------------------------------------------------   
select '客户账余情况查询' as '类型','0' as '余额' 
union all
select t.lx as '类型', t.ctotal'余额' from   
(  
select '返利账余' as lx
,isnull(aa.cardcode,'')U_cardcode
,case when (isnull(a.total,0)+isnull(c.AppTotal,0))=0 then 0 else isnull(a.total,0)+isnull(c.AppTotal,0) end  total
,isnull(b.LineTotal,0)-isnull(d.ktotal,0) as LineTotal 
,isnull(a.total,0)+isnull(c.AppTotal,0)+isnull(d.ktotal,0)-isnull(b.LineTotal,0) as ctotal 
from
OCRD aa 
left join (select U_cardcode,SUM(U_total)total from [@FLZY] group by U_cardcode) a on aa.CardCode=a.U_cardcode 
left join 
(select a.CardCode ,b.U_PSTYPE,sum(LineTotal)linetotal from ORDR a ,RDR1 b where a.DocEntry=b.DocEntry and a.CANCELED='N' and b.U_PSTYPE='返利订单' and b.LinManClsd='N'
group by a.CardCode ,b.U_PSTYPE  
) b on aa.cardcode=b.cardcode 
left join 
(
select cardcode ,sum(AppTotal)AppTotal  from REPDBD_DG..CostApp where IsCustCost='1' and zftype='返利帐余' and ApproStatus='Y'
group by cardcode
 )c on aa.cardcode =c.cardcode 
 left join 
(select a.CardCode ,b.U_PSTYPE,sum(LineTotal)ktotal 
from ORIN a ,RIN1 b where a.DocEntry=b.DocEntry and a.CANCELED='N' and b.U_PSTYPE='返利订单' and b.LinManClsd='N' 
group by a.CardCode ,b.U_PSTYPE  
)D on aa.cardcode=D.cardcode

 
 
union all 
 
select '随货搭赠账余' as lx
,isnull(aa.cardcode,'')U_cardcode
,isnull(a.total,0)+isnull(c.AppTotal,0) as total,isnull(b.LineTotal,0)-isnull(d.ktotal,0) as LineTotal 
,isnull(a.total,0)+isnull(c.AppTotal,0)+isnull(d.ktotal,0)-isnull(b.LineTotal,0) as ctotal 
from 
ocrd AA
left join(select U_cardcode,SUM(U_total)total from [@DZZY] group by U_cardcode) a  on aa.CardCode=a.U_cardcode 
left join (select a.CardCode ,b.U_PSTYPE,sum(LineTotal)linetotal 
from ORDR a ,RDR1 b where a.DocEntry=b.DocEntry and a.CANCELED='N' and b.U_PSTYPE='随货搭赠订单' and b.LinManClsd='N'
group by a.CardCode ,b.U_PSTYPE  
) b on aa.cardcode=b.cardcode  
left join 
(
select cardcode ,sum(AppTotal)AppTotal  from REPDBD_DG..CostApp where IsCustCost='1' and zftype='随货搭赠帐余' and ApproStatus='Y'
group by cardcode
 )c on aa.cardcode =c.cardcode
 left join 
 (select a.CardCode ,b.U_PSTYPE,sum(LineTotal)ktotal 
from ORIN a ,RIN1 b where a.DocEntry=b.DocEntry and a.CANCELED='N' and b.U_PSTYPE='随货搭赠订单' and b.LinManClsd='N' 
group by a.CardCode ,b.U_PSTYPE  
) d on aa.cardcode=d.cardcode 
   
union all 
 --现金账余--
select '标准订单额度' as lx,T1.cardcode as U_cardcode,T1.total,T2.LineTotal,isnull(T1.total,0)-isnull(T2.LineTotal,0) as ctotal from

(
 select aa.cardcode,sum(aa.total)total from
(
 select CardCode as cardcode,1000 as total
from OCRD 
where CardType='C'  ---信用额度1000

--union all
-- select cardcode,ISNULL(U_XY,0) as total
--from OCRD 
--where CardType='C'  ---临时信用额度

union all


select b.ShortName as cardcode
,case when b.Debit=0 and b.Credit>0 then b.Credit when b.Debit=0 and b.Credit<0  then -b.Credit
	  when b.Debit>0 and b.Credit=0 then -b.Debit when b.Debit<0 and b.Credit=0  then b.Debit else 0 end total
from OJDT a,JDT1 b,OCRD c 
where a.TransId=b.TransId  and b.ShortName=c.CardCode and (a.TransType='-2' or (a.TransType='30' and a.Memo like '%OB%')) and b.account='112201' and b.ShortName<>'YS001'  ---OB期初

union all
 select   t2.ShortName  as cardcode
,t2.Credit  as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId 
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')<>'' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  )
  )     ---收款确认上账
  union all
  select   t2.ShortName  as cardcode
,case when  t2.Credit<>0 then t2.Credit else -t2.Debit end   as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId 
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')='' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and ISNULL(t2.U_cgys,'')='2'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  where e.[DataSource] ='I'  )
  )  
  union all

select  t2.ShortName  as cardcode
 ,-(CASE when t2.Debit <>0 then t2.Debit else -t2.Credit end)  as total
 from  OJDT  t1 join JDT1 t2  on  t1.TransId =t2.TransId   
 join OCRD  t3 on t3.CardCode =t2.ShortName 
   where t2.U_KHKK ='2'    and t3.CardType ='C'  and t1.TransType ='30'
  and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e )
  )  and ISNULL(t1.[StornoToTr] ,0)=0
  
 --扣款
  
  
  
union all
select a.cardcode,a.SQJE as total
from REPDBD_DG.dbo.XYED a ,OCRD b
where 
a.cardcode=b.CardCode
and getdate()>=KSSJ AND getdate()<=JSSJ and ApproStatus='Y' ---OA发货担保

union all
select a.cardcode,a.AppTotal as total
from REPDBD_DG..CostApp a,OCRD b
where 
a.cardcode=b.CardCode
and IsCustCost='1' and zftype='现金账余' and ApproStatus='Y' ---OA特批费用核保上账

union all
select U_cardcode,sum(ISNULL(U_total,0))total from [@XJZY] group by U_cardcode  ---现金账余表
)aa 
group by aa.cardcode
)T1 --累计账余
left join 
(
 select bb.cardcode,sum(bb.total)LineTotal from
(
select a.cardcode,sum(b.LineTotal) as total
from ORDR a ,RDR1 b ,OCRD c  
where a.DocEntry=b.DocEntry  and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' or b.U_PSTYPE like '%现金订单%') and b.LinManClsd='N'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售订单
union All
select a.cardcode,-sum(b.LineTotal) as total
from ORIN a ,RIN1 b  ,OCRD c   
where a.DocEntry=b.DocEntry and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' or b.U_PSTYPE like '%现金订单%') and isnull(U_CloseBy,'')=''
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售贷项
)bb
group by bb.cardcode
)T2 --累计使用账余
on t1.cardcode =t2.CardCode 

union all --项目食品额度

select '项目食品额度' as lx,T10.cardcode as U_cardcode,isnull(T4.total,0),isnull(T5.LineTotal,0),isnull(T4.total,0)-isnull(T5.LineTotal,0) as ctotal from
ocrd t10 
LEFT JOIN
(
select bb.cardcode,sum(bb.total)LineTotal from
(
select a.cardcode,sum(b.LineTotal) as total
from ORDR a ,RDR1 b ,OCRD c ,oitm d 
where a.DocEntry=b.DocEntry  and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' ) and b.LinManClsd='N'
AND (b.itemcode = d.itemcode and d.itmsgrpcod = '103') and a.DocDate >= '20190901'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售订单
union All
select a.cardcode,-sum(b.LineTotal) as total
from ORIN a ,RIN1 b  ,OCRD c ,oitm d   
where a.DocEntry=b.DocEntry and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' ) and isnull(U_CloseBy,'')=''
AND (b.itemcode = d.itemcode and d.itmsgrpcod = '103') AND a.DocDate >= '20190901'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售贷项
)bb
group by bb.cardcode
)T5  on t10.cardcode=T5.cardcode --累计使用账余

left join 
(
 select aa.cardcode,sum(aa.total)total from
(
  select   t2.ShortName  as cardcode
,t2.Credit  as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId and t1.refdate > '20190801'
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')<>'' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and t2.LineMemo like '%项目食品款%'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  )
  )     ---收款确认上账
  union all
  select   t2.ShortName  as cardcode
,case when  t2.Credit>0 then t2.Credit else -t2.Debit end   as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId and t1.refdate > '20190801'
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')='' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and ISNULL(t2.U_cgys,'')='2'
 and t2.LineMemo like '%项目食品款%'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  where e.[DataSource] ='I'  )
  )  
  union all

select  t2.ShortName  as cardcode
 ,-(CASE when t2.Debit >0 then t2.Debit else -t2.Credit end)  as total
 from  OJDT  t1 join JDT1 t2  on  t1.TransId =t2.TransId  and t1.refdate > '20190801' 
 join OCRD  t3 on t3.CardCode =t2.ShortName 
   where t2.U_KHKK ='2'    and t3.CardType ='C'  and t1.TransType ='30'
   and t2.LineMemo like '%项目食品款%'
  and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e )
  )  and ISNULL(t1.[StornoToTr] ,0)=0
  --扣款  
union all
select U_cardcode,sum(ISNULL(U_total,0))total from [@SPXJZY] group by U_cardcode  ---食品上账表
)aa 
group by aa.cardcode
)T4
on t4.cardcode =t10.CardCode 
where t10.cardtype = 'C' and t10.frozenfor = 'N'


union all


select '果冻额度' as lx,T11.cardcode as U_cardcode,T11.total,T12.LineTotal,isnull(T11.total,0)-isnull(T12.LineTotal,0)-isnull(T14.Total,0)+isnull(T15.LineTotal,0) as ctotal from
(
 select aa.cardcode,sum(aa.total)total from
(
 select CardCode as cardcode,1000 as total
from OCRD 
where CardType='C'  ---信用额度1000

union all


select b.ShortName as cardcode
,case when b.Debit=0 and b.Credit>0 then b.Credit when b.Debit=0 and b.Credit<0  then -b.Credit
	  when b.Debit>0 and b.Credit=0 then -b.Debit when b.Debit<0 and b.Credit=0  then b.Debit else 0 end total
from OJDT a,JDT1 b,OCRD c 
where a.TransId=b.TransId  and b.ShortName=c.CardCode and (a.TransType='-2' or (a.TransType='30' and a.Memo like '%OB%')) and b.account='112201' and b.ShortName<>'YS001'  ---OB期初

union all
 select   t2.ShortName  as cardcode
,t2.Credit  as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId 
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')<>'' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  )
  )     ---收款确认上账
  union all
  select   t2.ShortName  as cardcode
,case when  t2.Credit<>0 then t2.Credit else -t2.Debit end   as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId 
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')='' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and ISNULL(t2.U_cgys,'')='2'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  where e.[DataSource] ='I'  )
  )  
  union all

select  t2.ShortName  as cardcode
 ,-(CASE when t2.Debit <>0 then t2.Debit else -t2.Credit end)  as total
 from  OJDT  t1 join JDT1 t2  on  t1.TransId =t2.TransId   
 join OCRD  t3 on t3.CardCode =t2.ShortName 
   where t2.U_KHKK ='2'    and t3.CardType ='C'  and t1.TransType ='30'
  and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e )
  )  and ISNULL(t1.[StornoToTr] ,0)=0
  
 --扣款
  
  
  
union all
select a.cardcode,a.SQJE as total
from REPDBD_DG.dbo.XYED a ,OCRD b
where 
a.cardcode=b.CardCode
and getdate()>=KSSJ AND getdate()<=JSSJ and ApproStatus='Y' ---OA发货担保

union all
select a.cardcode,a.AppTotal as total
from REPDBD_DG..CostApp a,OCRD b
where 
a.cardcode=b.CardCode
and IsCustCost='1' and zftype='现金账余' and ApproStatus='Y' ---OA特批费用核保上账

union all
select U_cardcode,sum(ISNULL(U_total,0))total from [@XJZY] group by U_cardcode  ---现金账余表
)aa 
group by aa.cardcode
)T11 --累计账余
left join 
(
 select bb.cardcode,sum(bb.total)LineTotal from
(
select a.cardcode,sum(b.LineTotal) as total
from ORDR a ,RDR1 b ,OCRD c  
where a.DocEntry=b.DocEntry  and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' or b.U_PSTYPE like '%现金订单%') and b.LinManClsd='N'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售订单
union All
select a.cardcode,-sum(b.LineTotal) as total
from ORIN a ,RIN1 b  ,OCRD c   
where a.DocEntry=b.DocEntry and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' or b.U_PSTYPE like '%现金订单%') and isnull(U_CloseBy,'')=''
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售贷项
)bb
group by bb.cardcode
)T12 --累计使用账余
on t11.cardcode =t12.CardCode 


left join 
(
 select aa.cardcode,sum(aa.total)total from
(
  select   t2.ShortName  as cardcode
,t2.Credit  as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId and t1.refdate > '20190801'
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')<>'' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and t2.LineMemo like '%项目食品款%'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  )
  )     ---收款确认上账
  union all
  select   t2.ShortName  as cardcode
,case when  t2.Credit>0 then t2.Credit else -t2.Debit end   as total
  from [dbo].[OJDT] t1 join [dbo].[JDT1] t2  on t1.TransId =t2.TransId and t1.refdate > '20190801'
  left join [dbo].[OCRD] t3 on t3.CardCode =t2.ShortName 
 where ISNULL (t1.[Ref3] ,'')='' and t2.[ShortName] <>'YS001'  and t2.ContraAct<>'21810199'  and ISNULL(t1.[StornoToTr] ,0)=0 
 and ISNULL(t2.U_cgys,'')='2'
 and t2.LineMemo like '%项目食品款%'
 and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e  where e.[DataSource] ='I'  )
  )  
  union all

select  t2.ShortName  as cardcode
 ,-(CASE when t2.Debit >0 then t2.Debit else -t2.Credit end)  as total
 from  OJDT  t1 join JDT1 t2  on  t1.TransId =t2.TransId  and t1.refdate > '20190801' 
 join OCRD  t3 on t3.CardCode =t2.ShortName 
   where t2.U_KHKK ='2'    and t3.CardType ='C'  and t1.TransType ='30'
   and t2.LineMemo like '%项目食品款%'
  and t1.[TransId] not in 
 (select w.[TransId]  from [dbo].[OJDT] w where w.[TransId] in 
 (select e.[StornoToTr] from [dbo].[OJDT] e )
  )  and ISNULL(t1.[StornoToTr] ,0)=0
  --扣款  
union all
select U_cardcode,sum(ISNULL(U_total,0))total from [@SPXJZY] group by U_cardcode  ---食品上账表
)aa 
group by aa.cardcode
)T14
on t14.cardcode =t11.CardCode 
LEFT JOIN
(
select bb.cardcode,sum(bb.total)LineTotal from
(
select a.cardcode,sum(b.LineTotal) as total
from ORDR a ,RDR1 b ,OCRD c ,oitm d 
where a.DocEntry=b.DocEntry  and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' ) and b.LinManClsd='N'
AND (b.itemcode = d.itemcode and d.itmsgrpcod = '103') and a.DocDate >= '20190901'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售订单
union All
select a.cardcode,-sum(b.LineTotal) as total
from ORIN a ,RIN1 b  ,OCRD c ,oitm d   
where a.DocEntry=b.DocEntry and a.cardcode=c.cardcode and a.CANCELED='N' and (b.U_PSTYPE like '%标准订单%' ) and isnull(U_CloseBy,'')=''
AND (b.itemcode = d.itemcode and d.itmsgrpcod = '103') AND a.DocDate >= '20190901'
group by  a.DocDate,a.cardcode,a.DocEntry   ---销售贷项
)bb
group by bb.cardcode
)T15  on t15.cardcode=T11.cardcode








)T  where isnull(t.U_cardcode,'') =@CardCode

GO