select * from (
select i.fcode,i.acode,i.cdate,i.year,i.month,i.nflags,i.keptbcode,i.icash,i.ocash,i.cremark,ii.initdate,isnull(b.rpbank,c.rpbank) rpbank,isnull(b.rpbankaccno,c.rpbankaccno) rpbankaccno,isnull(isnull(isnull(b.type,c.type),e.type) ,t.type)type,isnull(isnull(isnull(b.necode,c.necode),e.necode) ,t.necode) necode,isnull(b.ccode,c.ccode) as ccode,isnull(isnull(isnull(b.sheetcode,c.sheetcode),e.sheetcode),t.sheetcode)sheetcode
from
( select (isnull(icash,0)-isnull(ocash,0) +isnull(cbalance,0)) as cbalancex,cbalance,keptbcode,acode,(case when isnull(fcode,'') = '' then 'RMB' else fcode end) fcode ,nflags,cdate,cremark,icash,ocash,cpcode,gid,year(cdate) year,month,rerate,rpicode
from cash where (acode like '1001%' or acode like'1002%' or acode like '1012%' ) and (month is null or month<>0) ) i
left join
( select acode,initdate,keptbcode from cash where cbalance is not null and keptbcode<>'1' and (( cremark ='期初余额') and (month is null or month<>0)) ) ii
on i.acode = ii.acode and i.keptbcode = ii.keptbcode
left join
(
select a.* from (
select invicode,ccode,rpbank,rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(user_company,4) as keptbcode ,user_unfcy as fcy,dc,bcode,isnull(user_remark1,'合作费用') as type,invcode as necode from invoice
where status='70' and (sheetcode='1218113' or sheetcode='1218104'or sheetcode='1218111')
union
select invicode,''as ccode ,'' as rpbank,'' as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(user_company,4) as keptbcode ,user_unfcy as fcy,dc,bcode,(select cname from dictinfo where dictid=2 and code=paymode)as type,invcode as necode from invoice
where status>='40' and (sheetcode='1218002' or sheetcode='1218011' )
)a ) b on b.necode=left((right(i.cremark,len(i.cremark)-3)),len(i.cremark)-4)
left join
(
select a.* from (
select m.fineicode as invicode,isnull(m.ccode,null)as ccode,isnull(m.rpbank,null)as rpbank,isnull(m.rpbankccno,null)as rpbankaccno ,f.rerate,m.rmb,m.status,f.sheetcode,f.odate,m.fcode,left(f.bcode,4) as keptbcode ,f.fcy,m.dc,f.bcode,(select invname from invtypedef where invtype=f.fetype) as type,m.finecode as necode from finemain m full join finexp f on m.fineicode=f.innercode1 where
m.status='70'
)a )c on (c.necode=right(i.cremark,16) or c.necode=right((left(i.cremark,len(i.cremark)-1)),16))
left join
(
select a.* from (
select adjicode as invicode,''as rpbank,''as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(bcode,4) as keptbcode, fcy,'1' as dc,cashiercode as bcode,type,adjcode as necode from user_adjcash where status='70'
)a )t on t.keptbcode =i.keptbcode and (t.necode=right((left(i.cremark,len(i.cremark)-1)),13) or t.necode=right((left(i.cremark,len(i.cremark)-4)),len(i.cremark)-7) or t.necode=left((right(i.cremark,len(i.cremark)-3)),len(i.cremark)-4) )
left join
(select a.* from (
select invicode,''as rpbank,''as rpbankaccno ,rerate,rmb,status,sheetcode,odate,fcode,left(bcode,4) as keptbcode, fcy,'1' as dc,cashier as bcode,(select cname from dictinfo where code=paymode and dictid=2) as type,invcode as necode from jst_invoicex where status='70'
)a )e on e.keptbcode =i.keptbcode and (right(e.necode,13)=left(i.cremark,13) or e.necode=left(i.cremark,14))
where (i.cdate > isnull(ii.initdate,'1911-01-01') ) and len(i.cremark)>7
union all
select i.fcode,i.acode,i.cdate,i.year,i.month,i.nflags,i.keptbcode,i.icash,i.ocash,i.cremark,ii.initdate,'' as rpbank,'' as rpbankaccno,f.type as type,f.necode as necode,'' as ccode,'' as sheetcode
from
( select (isnull(icash,0)-isnull(ocash,0) +isnull(cbalance,0)) as cbalancex,cbalance,keptbcode,acode,(case when isnull(fcode,'') = '' then 'RMB' else fcode end) fcode ,nflags,cdate,cremark,icash,ocash,cpcode,gid,year(cdate) year,month,rerate,rpicode
from cash where (month is null or month<>0) ) i
left join
( select acode,initdate,keptbcode from cash where (( cremark ='期初余额') and (month is null or month<>0)) and cbalance is not null and keptbcode<>'1') ii