select
t.区域一级名称,
t.区域一级代码,
t.区域二级名称,
t.区域二级代码,
t.客户代码,
t.客户名称,
sum(isnull(t.应收金额,0)) as 应收金额,
sum(isnull(t.收款金额,0)) as 收款金额,
(sum(isnull(t.应收金额,0)) - sum(isnull(t.收款金额,0)) ) as 收款余额
from
(
select --TOP 100 PERCENT
t.区域一级名称,
t.区域一级代码,
t.区域二级名称,
t.区域二级代码,
t.fcunumber 客户代码,
t.fcuname 客户名称,
t.fbillno 单据编号,
case forder when 1 then case when len(ltrim(rtrim(t.fexplanation))) >0 then t.fexplanation else '销售' end
when 3 then case when len(ltrim(rtrim(t.fexplanation))) >0 then t.fexplanation else '银行收款' end
when 2 then case when len(ltrim(rtrim(t.fexplanation))) >0 then t.fexplanation else '优惠费用' end
end 摘要,
fdate 单据日期,
case forder when 1 then isnull(isnull(d.fname,f.fname),'') else '' end 产品名称,
isnull(c.fqty,e.fquantity) 数量,
isnull(case forder when 1 then isnull(c.fallamount,e.famount) when 2 then isnull(e.famount,t.famount) end,0) 应收金额,
case forder
when 1 then (case when (fcheckstatus=1 and fsalestyle=100) then isnull(c.fallamount,e.famount) else 0 end)
when 3 then t.famount
when 2 then 0
end as 收款金额 ,
--case forder when 3 then t.famount else 0 end as 收款金额,
case forder when 3 then t.FRemainAmount else 0 end as 收款余额,
fcheckstatus 核销标志,
forder,
isnull(c.fprice,e.fprice) as 单价,
isnull(c.fentryid,e.fentryid) as 发票序号
from
(
SELECT case ftype when 3 then 1 when 1 then 2 when 5 then 3 end as forder,--原3发票,5收款单,1其他应收单
a.finvoiceid,--发票内码
isnull(ics.fsalestyle,0) fsalestyle, --销售方式 100 现销 101 赊销
b.fnumber as fcunumber,
b.fname as fcuname,
a.fdate,
a.fstatus ,
a.FRemainAmount,
a.fnumber as fbillno, --单据编号,
isnull(a.fexplanation,'') fexplanation, --摘要,
a.famount, --收款金额
a.fcustomer,
case a.fstatus when 5 then 1 else 0 end fcheckstatus,
dbo.fn_GetNum(1,b.fnumber) as fcunumber1,
dbo.fn_GetNum(2,b.fnumber) as fcunumber2,
a.fbegid, --初始化单据内码
aa.fname as 区域一级名称,
aa.fnumber as 区域一级代码,
bb.fname as 区域二级名称,
bb.fnumber as 区域二级代码
FROM t_RP_Contact AS A--应收应付往来表
join t_item as b --客户基础资料
on a.fcustomer = b.fitemid
left join ICSale ics on ics.finterid=a.finvoiceid --发票中销售方式
--and a.FRP = 1
join t_item as aa on aa.fitemclassid = 1 and aa.fnumber =dbo.fn_GetNum(1,b.fnumber)
join t_item as bb on bb.fitemclassid = 1 and bb.fnumber = dbo.fn_GetNum(2,b.fnumber)
) as t
left join ICSALEENTRY as c on c.finterid = t.finvoiceid
left join t_icitem as d on c.fitemid = d.fitemid
left join t_rp_BegDataentry as e on e.finterid = t.FBEGID
left join t_icitem as f on e.fproductid = f.fitemid
--order by t.fcunumber,fcheckstatus desc,fdate,t.fbillno,forder,c.fentryid
)
as t
group by t.区域一级名称,
t.区域一级代码,
t.区域二级名称,
t.区域二级代码,
t.客户代码,
t.客户名称
