begin
select distinct accidname,companyname,hbcompanyname
into #temp_CO_JC_Account
from [dbo].[CO_JC_Account]
where 1=1
and hbcompanyname in ($(合并报表_本方单位))
select tt.*,t1.igrade,t1.ccode as ccode2
into #temp_kemuyuechuantou111
from
(
select dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,ruleversion,rownum,indexname,ccode,jdfx,jsfs,hesuan
from [dbo].[VW_CO_JC_BalanceSheet_zb]
where 1=1
and accidname in (select accidname from #temp_CO_JC_Account)
and iyear=$(合并报表-年度)
and iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )
and isbook='$(合并报表-是否包含未记账)'
$(合并报表_报表行号)
$(合并报表_指标名称2)
union all
select dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,ruleversion,rownum,indexname,ccode,jdfx,jsfs,NULL as hesuan
from [dbo].[VW_CO_JC_Profit_zb]
where 1=1
and accidname in (select accidname from #temp_CO_JC_Account)
and iyear=$(合并报表-年度)
and iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )
and isbook='$(合并报表-是否包含未记账)'
$(合并报表_报表行号)
$(合并报表_指标名称2)
)as tt
left join
(
select distinct dbtype,dbName ,companyName,accidname,ccode,igrade,iyear
from CO_JC_Ccode
where 1=1
and accidname in (select accidname from #temp_CO_JC_Account)
and iyear=$(合并报表-年度)
) as t1 on tt.dbtype=t1.dbtype and tt.accidname=t1.accidname and tt.iyear=t1.iyear and left(t1.ccode,len(tt.ccode))=tt.ccode
where 1=1
and (case when len(isnull(tt.jdfx,N''))<1 and len(isnull(tt.hesuan,N''))<1 then tt.ccode else t1.ccode end) =t1.ccode
select
cast(tt.iyear as varchar(10))+'年'+cast(tt.iperiod as varchar(4))+'月' as iyperiod,
tt.dbtype,tt.dbName ,tt.companyName,tt.accidname,tt.iyear,tt.iperiod,
accountTypeID,accountTypeName,accountCode,
currencyCode,currencyName,igrade,
case when igrade=6 then N' '+accountCode2 when igrade=5 then N' '+accountCode2 when igrade=4 then N' '+accountCode2 when igrade=3 then N' '+accountCode2 when igrade=2 then N' '+accountCode2
else accountCode2 end as accountCode2,
accountName,ccodefx,
case when isdetail=0 then
(case when igrade=6 then N' '+accountName when igrade=5 then N' '+accountName when igrade=4 then N' '+accountName when igrade=3 then N' '+accountName when igrade=2 then N' '+accountName
else accountName end) else (case when igrade=6 then N' '+accountName2 when igrade=5 then N' '+accountName2 when igrade=4 then N' '+accountName2 when igrade=3 then N' '+accountName2 when igrade=2 then N' '+accountName2
else accountName2 end) end as accountName2,
localqc,localjfqc,localdfqc,localjffs,localdffs,localytdjffs,localytddffs,localqm,localjfqm,localdfqm
from
(
SELECT
dbtype,dbName ,companyName,accidname,iyear,iperiod,isbook,
accountTypeID,accountTypeCode,accountTypeName,calDirection,
--currencyID,accountID,
currencyCode,currencyName,
accountCode,accountName,
(Case When len(isnull(isauxaccdepartmentcode,N''))<1 then N'' Else N' [' + isnull(isauxaccdepartmentcode,N'') + N'] ' End) + ISNULL(isauxaccdepartment, N'')
+ (Case When len(isnull(isauxaccpersoncode,N''))<1 then N'' Else N' [' + isnull(isauxaccpersoncode,N'') + N'] ' End) + ISNULL(isauxaccperson, N'')
+ (Case When len(isnull(isauxacccustomercode,N''))<1 then N'' Else N' [' + isnull(isauxacccustomercode,N'') + N'] ' End) + ISNULL(isauxacccustomer, N'')
+ (Case When len(isnull(isauxaccinventorycode,N''))<1 then N'' Else N' [' + isnull(isauxaccinventorycode,N'') + N'] ' End) + ISNULL(isauxaccinventory, N'')
+ (Case When len(isnull(isauxaccprojectcode,N''))<1 then N'' Else N' [' + isnull(isauxaccprojectcode,N'') + N'] ' End) + ISNULL(isauxaccproject, N'')
+ (Case When len(isnull(isexauxacc1code,N''))<1 then N'' Else N' [' + isnull(isexauxacc1code,N'') + N'] ' End) + ISNULL(isexauxacc1, N'')
+ (Case When len(isnull(isexauxacc2code,N''))<1 then N'' Else N' [' + isnull(isexauxacc2code,N'') + N'] ' End) + ISNULL(isexauxacc2, N'')
+ (Case When len(isnull(isexauxacc3code,N''))<1 then N'' Else N' [' + isnull(isexauxacc3code,N'') + N'] ' End) + ISNULL(isexauxacc3, N'')
+ (Case When len(isnull(isexauxacc4code,N''))<1 then N'' Else N' [' + isnull(isexauxacc4code,N'') + N'] ' End) + ISNULL(isexauxacc4, N'')
+ (Case When len(isnull(isexauxacc5code,N''))<1 then N'' Else N' [' + isnull(isexauxacc5code,N'') + N'] ' End) + ISNULL(isexauxacc5, N'')
+ (Case When len(isnull(isexauxacc6code,N''))<1 then N'' Else N' [' + isnull(isexauxacc6code,N'') + N'] ' End) + ISNULL(isexauxacc6, N'')
+ (Case When len(isnull(isexauxacc7code,N''))<1 then N'' Else N' [' + isnull(isexauxacc7code,N'') + N'] ' End) + ISNULL(isexauxacc7, N'')
+ (Case When len(isnull(isexauxacc8code,N''))<1 then N'' Else N' [' + isnull(isexauxacc8code,N'') + N'] ' End) + ISNULL(isexauxacc8, N'')
+ (Case When len(isnull(isexauxacc9code,N''))<1 then N'' Else N' [' + isnull(isexauxacc9code,N'') + N'] ' End) + ISNULL(isexauxacc9, N'')
+ (Case When len(isnull(isexauxacc10code,N''))<1 then N'' Else N' [' + isnull(isexauxacc10code,N'') + N'] ' End) + ISNULL(isexauxacc10, N'') as accountName2,
case when accountDcdirection='652' then '借' else '贷' end as ccodefx,
--accountID2,accountUnit,
case when isdetail=0 then accountCode else N'' end as accountCode2,
isEndNode,isdetail,
isauxacccustomer,isauxaccdepartment,
isauxaccperson,isauxaccproject,isauxaccinventory,
isexauxacc1,isexauxacc2,isexauxacc3,isexauxacc4,isexauxacc5,
isexauxacc6,isexauxacc7,isexauxacc8,isexauxacc9,isexauxacc10,
--quantityBalance,origAmountBalance,
amountBalance as localqc,
--case when amountBalance>0 then '借' when amountBalance<0 then '贷' else '平' end as localqcfx,
case when amountBalance>0 then amountBalance else 0 end as localjfqc,
case when amountBalance<0 then abs(amountBalance) else 0 end as localdfqc,
--quantityDr,quantityCr,origAmountDr,origAmountCr,
amountDr as localjffs,
amountCr as localdffs,
--cumQuantityDr,cumQuantityCr,
--cumOrigAmountDr,cumOrigAmountCr,
cumAmountDr as localytdjffs,cumAmountCr as localytddffs,
--endquantityBalance,endorigAmountBalance,
endamountBalance as localqm,
case when endamountBalance>0 then endamountBalance else 0 end as localjfqm,
case when endamountBalance<0 then abs(endamountBalance) else 0 end as localdfqm
FROM CO_JC_ERP_Balance
where 1=1
and accidname in (select accidname from #temp_CO_JC_Account)
and ISNULL(isauxacccustomer, N'') in ($(合并报表_对方单位))
and iyear=$(合并报表-年度)
and iperiod=(case when $(合并报表-期间) =0 then 1 else $(合并报表-期间) end )
and isbook='$(合并报表-是否包含未记账)'
) as tt
inner join #temp_kemuyuechuantou111 as t1 on tt.dbtype=t1.dbtype and tt.accidname=t1.accidname and tt.iyear=t1.iyear
and left(tt.accountcode,len(t1.ccode2))=t1.ccode2
--and tt.accountcode=t1.ccode2
order by accounttypecode,accountcode,igrade, accountName2
drop table #temp_kemuyuechuantou111
drop table #temp_CO_JC_Account
end