财务对账-资金统计

select dtime1,SUM(fkje) as fkje,
(select sum(borrow_amount) from yyd_borrow_borrowinfo where (status=10 or status=20) and loandate<=tongjitb.dtime1
--张亮修改,是否属于内部用户11.5

) sumFKJE,
(select sum(amount) from yyd_Account_moneyrecord where state=1 and (moneytype='充值' or moneytype='线下充值') and paytime<=tongjitb.dtime1
--张亮修改,是否属于内部用户11.5

) sumCZJE
,SUM(czje) as czje,
SUM(txje) as txje,
(select sum(amount) from yyd_Account_moneyrecord where state=1 and (moneytype='提现'or moneytype='线下提现') and createtime<=tongjitb.dtime1
--张亮修改,是否属于内部用户11.5

) sumTXJE,
SUM(ptje) as ptje,
(select sum(jfmoney) from HUR_PlatFormFinanceDetail where feeDate<=dtime1 and fType=3
--张亮修改,是否属于内部用户11.5

) sumPTJE,
SUM(lxje) as lxje,
(select sum(repay_Amount) from yyd_borrow_repayrecord where repaytype='利息' and repay_time<=tongjitb.dtime1
--张亮修改,是否属于内部用户11.5

)sumLXJE,
SUM(sxfje) as sxfje,
(select SUM( amount *( case recharge_type when 'chinapaywap' then 0.01 when 'chinapay_b2c' then 0.005 when '0' then 0.005 else 0 end) )from YYD_Account_RechargeRecord where state=1 and createtime<=dtime1
--张亮修改,是否属于内部用户11.5

)sumSXFJE,
--(select ISNULL(usableamount,0)+ISNULL(withdrawing,0) from (select sum(usableamount) usableamount,(select SUM(amount) from YYD_Account_MoneyRecord where (moneytype='提现' or moneytype='线下提现') and state=0
----张亮修改,是否属于内部用户11.5

--) withdrawing from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)
----张亮修改,是否属于内部用户11.5

--) data) UsableTotal,

--2016-07-14修改备兑总金额:UsableTotal =可用余额+冻结(UsableAmount+FrozenAmount)
(select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance
where CONVERT(datetime,period)=CONVERT(datetime,dtime1)) as UsableTotal,

 

--(select sum(balanceamount) from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)
----张亮修改,是否属于内部用户11.5
--) balanceAmount,
--2016-07-14修改--账户余额:balanceAmount = 可用余额总金额(UsableAmount)
(select sum(UsableAmount) from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,dtime1)
) balanceAmount,

(select sum(repay_amount) from YYD_Borrow_RepayRecord where repay_time>=dtime1 and repaytype='本金' ) DSBJAmount
--待收本金
from
(select CONVERT(datetime, ISNULL(ISNULL(ISNULL(tt1.dt1,(ISNULL(tt1.dt2,tt1.dt3))),tt1.dt5),tt1.dt6)) as dtime1 ,tt1.fkje,tt1.czje,tt1.txje,tt1.ptje,tt1.lxje,tt1.sxfje from
(
select * from
(
--标的详情主表（放款金额）
select SUM (borrow_amount) as fkje,convert(nvarchar(10),loandate,120) dt1 from YYD_Borrow_BorrowInfo
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),loandate,120)
) as zb
full join
--平台费
(
select sum(jfmoney) ptje,convert(nvarchar(10),feeDate,120) feeDate from HUR_PlatFormFinanceDetail where ftype=3
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),feeDate,120)
)
as pt
on pt.feeDate=zb.dt1
full join
( ---充值金额
select convert(nvarchar(10),paytime,120) dt2,SUM(amount) czje from YYD_Account_MoneyRecord where (moneytype='充值' or moneytype='线下充值') and state=1
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),paytime,120)
) as cz
on zb.dt1=cz.dt2
full join
----开始其他的表合并
(
--手续费表
select convert(nvarchar(10),createtime,120) dt6, SUM( amount *( case recharge_type when 'chinapaywap' then 0.01 when 'chinapay_b2c' then 0.005 when '0' then 0.005 else 0 end) ) as sxfje from YYD_Account_RechargeRecord
where state=1
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),createtime,120)
) as sxf on zb.dt1=sxf.dt6
full join
(
--提现表
select convert(nvarchar(10),createtime,120) dt3,SUM(amount) txje from YYD_Account_MoneyRecord where (moneytype='提现' or moneytype='线下提现') and state=1
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),createtime,120)
) as tx on zb.dt1=tx.dt3
full join
(
--利息表
select convert(nvarchar(10),repay_time,120) dt5,SUM(repay_amount) lxje from YYD_Borrow_RepayRecord where status=1 and repaytype='利息'
--张亮修改,是否属于内部用户11.5

group by convert(nvarchar(10),repay_time,120)
) as lx on zb.dt1=lx.dt5
)as tt1
) as tongjitb

group by dtime1 order by dtime1 desc

 

--注释说明对应的字段如下：
--备兑金额:UsableTotal =可用+冻结(UsableAmount+FrozenAmount)
--账户余额:balanceAmount = 可用余额总金额(UsableAmount)
--待收本金:DSBJAmount

--(select ISNULL(usableamount,0)+ISNULL(withdrawing,0) from
--(select sum(usableamount) usableamount,(select SUM(amount) from YYD_Account_MoneyRecord
--where (moneytype='提现' or moneytype='线下提现') and state=0
--)
-- withdrawing from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2016-07-13 00:00:00.000')
--) data) UsableTotal


-- select * from hur_accountinfo_balance order by ID desc

-- select UsableAmount+FrozenAmount from(
-- (select SUM(UsableAmount) as UsableAmount from hur_accountinfo_balance
-- where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')) sss2,
-- (select SUM(FrozenAmount) as FrozenAmount from hur_accountinfo_balance
-- where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')) sss
--)

-- select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')


-- select ss=ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0) from hur_accountinfo_balance


-- select sum(ISNULL(UsableAmount,0)+ISNULL(FrozenAmount,0)) as ddd from hur_accountinfo_balance

-- select sum(UsableAmount) as balanceAmount from hur_accountinfo_balance where CONVERT(datetime,period)=CONVERT(datetime,'2015-10-15 00:00:00.000')