----------------------------------------------------------------------------------------
--私奔到火星总预算会计标准表
--编制时间:年月
--编制人:爱玩儿の编程小菜机
--数据源:(私奔到火星私奔到火星私奔到火星总预算会计年度备份账套)ZFKJ2005_ZFKJ2005_2018YEAR
--执行结果:生成(私奔到火星总预算会计标准表)DZ_ZYSKJ_BZB_2018
----------------------------------------------------------------------------------------
--执行过程:
--第一步,找表:
--1.查看每张表的行数;
select schema_name(t.schema_id) as [Schema], t.name as TableName,i.rows as [RowCount]
from sys.tables as t, sysindexes as i
where t.object_id = i.id and i.indid <=1
order by 3 desc
go
--2.依次查看每张表及其主键;
--(1)凭证表--FLM
select *
from ZF1PD
--(2)会计科目与对应单位
select *
from ZF1STRUCT_ZZ_DW
--会计科目 单位
--ZZ_SCM DW_SCM
--5211 8777
--(3)功能分类科目表(其中:SCM字段对应凭证表KM_SCM字段)--SCM
select *
from ZF1KM01
--(4)功能分类科目与对应单位
select *
from ZF1STRUCT_KM_DW
--功能分类科目 单位
--KM_SCM DW_SCM
--9052 3091
--(5)操作记录
select *
from ZF1EVENT
--(6)业务部门(即私奔到火星归口股室)与对应单位
select *
from [ZF1STRUCT_ZG_DW]
--业务部门 单位
--ZG_SCM DW_SCM
--3197 2813
--(7)单位编码表(含修改时间)--SCM
select *
from ZF1DW01
--(8)单位编码表中的KMH对应该表中的VAL
--单位编码表中的SCM对应该表中的SCM
select *
from ZF1DY
--TYPE FIELDNAME VAL DY_FIELD SCM
--0 DW_SCM 208223 DWH 2955
--0 KM_MJ_SCM 50101 KMMJH 12459
--(9)基础信息
select *
from BM1PARM
--(10)***与业务部门对应关系表
select *
from ZF1STRUCT_KM_ZG
--*** 业务部门
--KM_SCM ZG_SCM
--10241 3197
--(11)科目表--SCM
--包含SCM,NAME科目名称,KMH科目号,ATT科目类型,JC科目级次
--JD_FLAG借贷方向,SCM_1等信息的科目表
--275行
select *
from ZF1ZZ01
--(12)视图及数据源表--(重要)
select *
from BB1VIEW_FIELD
--(13)经济分类科目表--SCM
select *
from ZF1KM_MJ01
--(14)人员做账记录
select *
from ZF1USER_MENU
--(15)银行账户信息
select *
from ZF1ZHH
--(16)其他信息
select *
from BB_PARM
--(17)其他信息
select *
from ZF1BK
--(18)归口股室/业务部门--SCM
select *
from ZF1ZG01
--(19)其他信息
select *
from BB1VIEW01
go
--3.根据上述找到的五张表,结合视图分析连接条件:
--凭证表 ZF1PD
--科目表 ZF1ZZ01
--功能分类科目表 ZF1KM01
--单位编码表 ZFLX_SCM
--经济分类科目表 ZF1KM_MJ01
--()查看视图:
select * from BB1VIEW_FIELD where VIEW_NAME='V_BM3_PD_ALL';--(重要)
--()根据视图,对凭证表字段名及连接条件进行分析:
--字段名 --连接表 --连接字段
SELECT FLM --主键 -- 分录码
, ZZ_SCM --关键字 -- 会计科目 ZF1ZZ01 SCM
, KM_SCM --关键字 -- 预算科目 ZF1KM01 SCM
, DW_SCM --关键字 -- 单位编码 ZF1DW01 SCM
, LY_SCM --关键字 -- 资金来源 ZF1LY01 SCM
, KM_MJ_SCM --关键字 -- 经济科目 ZF1KM_MJ01 SCM
, XM_SCM --关键字 -- 项目 ZF1XM01 SCM
, ZG_SCM --关键字 -- 业务部门 ZF1ZG01 SCM
, ZJXZ_SCM --关键字 -- 资金来源 ZF1LY01 SCM
, ZFLX_SCM --关键字 -- 支付类型 ZF1ZFLX01 SCM
, JFLX_SCM --关键字 -- 经费类型 ZF1JFLX01 SCM
, MONTH -- 月
, DAY -- 日
, PDH -- 凭单号
, FLH -- 分录号
, JDF -- 借贷方
, FDJ -- 附单据数
, ZY -- 摘要
, ZY_MX -- 明细摘要
, VAL -- 凭单金额(考虑了借贷方和总帐属性的凭单金额)
, KEEPFLAG -- 登帐标志
, ZDR -- 制单人
, ZDR_TIME -- 制单日期
, JZR -- 记账人
, JZR_TIME -- 记账日期
, FHR --
, FHR_TIME --
, ZGR --
, ZGR_TIME --
, JC_RECNO --
, SX1_SCM -- 属性
, SX2_SCM -- 属性
, SX3_SCM -- 属性
, WB1 -- 文本
, WB2 -- 文本
, WB3 -- 文本
, ZHUANZHANG --
, ZB_ID --
, MODIFY_TIME --
from ZF1PD
--第二步,测试各表连接凭证表:
SELECT FLM --主键 -- 分录码
, ZZ_SCM --关键字 -- 会计科目 ZF1ZZ01 SCM
, KM_SCM --关键字 -- 预算科目 ZF1KM01 SCM
, DW_SCM --关键字 -- 单位编码 ZF1DW01 SCM
, KM_MJ_SCM --关键字 -- 经济科目 ZF1KM_MJ01 SCM
, ZG_SCM --关键字 -- 业务部门 ZF1ZG01 SCM
, MONTH -- 月
, DAY -- 日
, PDH -- 凭单号
, FLH -- 分录号
, JDF -- 借贷方
, ZY -- 摘要
, ZY_MX -- 明细摘要
, VAL -- 凭单金额(考虑了借贷方和总帐属性的凭单金额)
, ZDR_TIME -- 制单日期
, km.SCM km_SCM
, km.NAME km_NAME
, ys.SCM ys_SCM
, ys.NAME ys_NAME
, dw.SCM dw_SCM
, dw.NAME dw_NAME
, jj.SCM jj_SCM
, jj.NAME jj_NAME
, case when km.ATT='收入' then km.SCM end sr_SCM
, case when km.ATT='收入' then km.NAME end sr_NAME
from ZF1PD pz left join ZF1ZZ01 km -- 会计科目
on pz.ZZ_SCM = km.scm
left join ZF1KM01 ys -- 功能科目(预算科目)
on pz.KM_SCM = ys.scm
left join ZF1DW01 dw -- 单位编码
on pz.DW_SCM = dw.scm
left join ZF1KM_MJ01 jj -- 经济科目
on pz.KM_MJ_SCM = jj.scm
--where km.ATT='收入'
--收入科目字段需要从会计科目中提取出来;
select ATT
from ZF1ZZ01
group by ATT
--执行结果:
--ATT
--负债
--净资产
--收入
--支出
--资产
select *
from ZF1ZZ01
where ATT='收入'
--第三步,生成总预算会计标准表(仅含末级科目名称):
select
'我叫地区编号' XZQHDM, --行政区划代码
'私奔到火星' XZQHMC, --行政区划名称
'[ZFKJ2005_ZFKJ2005_2018YEAR]' KJZTCODE, --会计账套代码
'私奔到火星私奔到火星总预算会计年度' KJZTNAME, --会计账套名称
'2018' KJND, --会计年度
(
case
when len(MONTH)=1 then '0'+cast(MONTH as varchar(2))
else cast(MONTH as varchar(2))
end
) KJQJ, --会计期间
'记账凭证' PZLX, --凭证类型
PDH PZCODE, --凭证编号
cast(replace(ZDR_TIME,'-','') as CHAR(8))
PZDATE, --凭证日期
FLH PZFLH, --凭证分录号
km.KMH KMCODE, --科目编码
km.NAME KMNAME, --科目名称
(
case
when km.ATT='支出' then ys.KMH --(会计科目为支出类)
else null
end
) GNFLCODE, --功能科目(仅用于支出类)
(
case
when km.ATT='支出' then ys.NAME
else null
end
) GNFLNAME, --功能科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='支出' then jj.KMH --(会计科目为支出类)
else null
end
) JJFLCODE, --经济科目(仅用于支出类)
(
case
when km.ATT='支出' then jj.NAME
else null
end
) JJFLNAME, --经济科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='收入' then km.KMH --(会计科目为支出类)
else null
end
) SRKMCODE, --收入科目(仅用于支出类)
(
case
when km.ATT='收入' then km.NAME
else null
end
) SRKMNAME, --收入科目名称
---------------------------------------------------------------------------------
(
case
when ZY<>ZY_MX then ZY+'('+ZY_MX +')'
else ZY
end
) ZHAIYAO, --摘要
(
case
when JDF=1 then VAL
else 0
end
) JFJE, --借方金额
(
case
when JDF=-1 then VAL
else 0
end
) DFJE, --借方金额
WB1+WB2+WB3 BZ --备注
--INTO DZ_ZYSKJ_BZB_2018 --生成:火星_总预算会计_标准表_2018
from ZF1PD pz left join ZF1ZZ01 km -- 会计科目
on pz.ZZ_SCM = km.scm
left join ZF1KM01 ys -- 功能科目(预算科目)
on pz.KM_SCM = ys.scm
left join ZF1DW01 dw -- 单位编码(因未含全部科目名称,此条件可删除)
on pz.DW_SCM = dw.scm
left join ZF1KM_MJ01 jj -- 经济科目
on pz.KM_MJ_SCM = jj.scm
WHERE km.ATT='收入'
--第四步,生成总预算会计标准表(科目名称包含上级科目未含单位):
--1.生成含上级科目名的会计科目视图ZF1ZZ01_S
alter table ZF1ZZ01
alter column NAME nvarchar(13);
go
create view ZF1ZZ01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.ATT,a.JC,a.JD_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1ZZ01 a
left join ZF1ZZ01 b on b.SCM=a.SCM_1
left join ZF1ZZ01 c on c.SCM=a.SCM_2
left join ZF1ZZ01 d on d.SCM=a.SCM_3
left join ZF1ZZ01 e on e.SCM=a.SCM_4
left join ZF1ZZ01 f on f.SCM=a.SCM_5
--order by a.KMH
GO
--2.生成含上级科目名的预算科目(功能科目)视图ZF1KM01_S
alter table [ZF1KM01]
alter column NAME nvarchar(30);
go
create view ZF1KM01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.JC,a.JC_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1KM01 a
left join ZF1KM01 b on b.SCM=a.SCM_1
left join ZF1KM01 c on c.SCM=a.SCM_2
left join ZF1KM01 d on d.SCM=a.SCM_3
left join ZF1KM01 e on e.SCM=a.SCM_4
left join ZF1KM01 f on f.SCM=a.SCM_5
--order by a.KMH
go
--3.生成含上级科目名的经济科目视图ZF1KM_MJ01_S
alter table ZF1KM_MJ01
alter column NAME nvarchar(30);
go
create view ZF1KM_MJ01_S
as
select a.SCM,a.KMH,a.SCM_1,a.SCM_2,a.SCM_3,a.SCM_4,a.SCM_5,a.JC,a.JC_FLAG,
case
when a.JC=1 then b.NAME
when a.JC=2 then replace((b.NAME+'\'+c.NAME),' ','')
when a.JC=3 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME),' ','')
when a.JC=4 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME),' ','')
when a.JC=5 then replace((b.NAME+'\'+c.NAME+'\'+d.NAME+'\'+e.NAME +'\'+f.NAME),' ','')
end NAME
from ZF1KM_MJ01 a
left join ZF1KM_MJ01 b on b.SCM=a.SCM_1
left join ZF1KM_MJ01 c on c.SCM=a.SCM_2
left join ZF1KM_MJ01 d on d.SCM=a.SCM_3
left join ZF1KM_MJ01 e on e.SCM=a.SCM_4
left join ZF1KM_MJ01 f on f.SCM=a.SCM_5
--order by a.KMH
--4.生成总预算会计标准表DZ_ZYSKJ_2018
select
'我叫地区编号' XZQHDM, --行政区划代码
'私奔到火星' XZQHMC, --行政区划名称
'[ZFKJ2005_ZFKJ2005_2018YEAR]' KJZTCODE, --会计账套代码?
'私奔到火星私奔到火星总预算会计年度' KJZTNAME, --会计账套名称?
'2018' KJND, --会计年度
(
case
when len(MONTH)=1 then '0'+cast(MONTH as varchar(2))
else cast(MONTH as varchar(2))
end
) KJQJ, --会计期间
'记账凭证' PZLX, --凭证类型
PDH PZCODE, --凭证编号
cast(replace(ZDR_TIME,'-','') as CHAR(8))
PZDATE, --凭证日期
FLH PZFLH, --凭证分录号
km.KMH KMCODE, --科目编码
km.NAME KMNAME, --科目名称
(
case
when km.ATT='支出' then ys.KMH --(会计科目为支出类)
else null
end
) GNFLCODE, --功能科目(仅用于支出类)
(
case
when km.ATT='支出' then ys.NAME
else null
end
) GNFLNAME, --功能科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='支出' then jj.KMH --(会计科目为支出类)
else null
end
) JJFLCODE, --经济科目(仅用于支出类)
(
case
when km.ATT='支出' then jj.NAME
else null
end
) JJFLNAME, --经济科目名称
---------------------------------------------------------------------------------
--新增字段:
(
case
when km.ATT='收入' then km.KMH --(会计科目为支出类)
else null
end
) SRKMCODE, --收入科目(仅用于支出类)
(
case
when km.ATT='收入' then km.NAME
else null
end
) SRKMNAME, --收入科目名称
---------------------------------------------------------------------------------
(
case
when ZY<>ZY_MX then ZY+'('+ZY_MX +')'
else ZY
end
) ZHAIYAO, --摘要
(
case
when JDF=1 then VAL
else 0
end
) JFJE, --借方金额
(
case
when JDF=-1 then VAL
else 0
end
) DFJE, --借方金额
WB1+WB2+WB3 BZ --备注
--INTO DZ_ZYSKJ_BZB_2018 --生成:火星_总预算会计_标准表_2018
from ZF1PD pz left join ZF1ZZ01_S km -- 会计科目
on pz.ZZ_SCM = km.scm
left join ZF1KM01_S ys -- 功能科目(预算科目)
on pz.KM_SCM = ys.scm
-- left join ZF1DW01 dw -- 单位编码(因会计科目未包含具体单位,此条件可删除)
-- on pz.DW_SCM = dw.scm
left join ZF1KM_MJ01_S jj -- 经济科目
on pz.KM_MJ_SCM = jj.scm
----------------------------------------------------------------------------------------
--第五步:新建数据库,将生成的总预算会计标准表导入,并备份: