saas-one/script/mysql/biz/finance.sql

create table `voucher_ep`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
content text not null comment '摘要内容',
creator_id int unsigned,
creator_name varchar(100),
create_time datetime,
updater_id int unsigned,
updater_name varchar(100),
update_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证摘要';

create table `voucher_tpl_type`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
name varchar(255) not null comment '类别名称',
creator_id int unsigned,
creator_name varchar(100),
create_time datetime,
updater_id int unsigned,
updater_name varchar(100),
update_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证模板类别';

create table `voucher_tpl`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
type_id int unsigned not null comment '类别',
name varchar(255) not null comment '模板名称',
enabled boolean default true comment '是否启用',
creator_id int unsigned,
creator_name varchar(100),
create_time datetime,
updater_id int unsigned,
updater_name varchar(100),
update_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证模板';

create table `voucher_tpl_item`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
tpl_id int unsigned not null comment '模板ID',
vd_detno int comment '序号',
vd_explanation varchar(255) comment '摘要',
vd_catecode varchar(30) comment '科目号',
vd_currency varchar(30) comment '币别',
vd_debit double comment '借方金额',
vd_credit double comment '贷方金额',
vd_flowcode varchar(30) comment '现金流量项目',
creator_id int unsigned,
creator_name varchar(100),
create_time datetime,
updater_id int unsigned,
updater_name varchar(100),
update_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证模板分录';

create table `voucher_tpl_ass`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
tpl_id int unsigned not null comment '模板ID',
item_id int unsigned not null comment '分录ID',
vds_asstype varchar(30),
vds_assid int,
vds_asscode varchar(50),
vds_assname varchar(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证模板辅助核算';

create table `voucher_mark`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
order_num int unsigned not null comment '顺序',
name varchar(50) not null comment '凭证字',
print_title varchar(255) comment '打印标题',
default_use boolean comment '是否默认',
creator_id int unsigned,
creator_name varchar(100),
create_time datetime,
updater_id int unsigned,
updater_name varchar(100),
update_time datetime
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='凭证字';

create table `fin_report_item`
(
report_type varchar(30) not null comment '报表类型balance, income, cashflow',
item_code varchar(30) not null comment '报表项目编号',
item_name varchar(255) comment '报表项目名称',
item_type int default 0 comment '项目计算类型, 0 - 不计算, 1 - 取rule表数据, 2 - 按formula计算',
order_num int comment '显示顺序',
rownum int comment '行次',
formula varchar(255) comment '计算公式'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='财务报表';

create table `fin_report_rule`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
report_type varchar(30),
item_code varchar(30),
ca_id int unsigned not null,
symbol char(1) comment '运算符号+/-',
rule int comment '取数规则, 1 - 余额, 2 - 借方余额, 3 - 贷方余额, 4 - 科目借方余额, 5 - 科目贷方余额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='财务报表取数规则';

create table `fin_cashflow_item`
(
cl_itemcode varchar(30) comment '现金流量项目编号',
cl_itemname varchar(255) comment '现金流量项目名称',
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='现金流量项目';

create table `fin_cashflow_ledger`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
cl_yearmonth int comment '期间',
cl_itemcode varchar(30) comment '现金流量项目编号',
cl_itemname varchar(255) comment '现金流量项目名称',
cl_begin double comment '期初余额',
cl_this double comment '本期发生',
cl_end double comment '期末余额',
cl_accum double comment '本年累计'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='现金流量余额';

create table `fin_profit_ledger`
(
id int unsigned primary key not null auto_increment,
company_id int unsigned not null,
pl_yearmonth int comment '期间',
pl_cacode varchar(30) comment '科目编号',
pl_caname varchar(255) comment '科目名称',
pl_caid int comment '科目ID',
pl_begin double comment '上期发生',
pl_this double comment '本期发生',
pl_balancetype varchar(10) comment '方向',
pl_accum double comment '本年累计'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='损益发生';

create table `fin_report_data`
(
company_id int unsigned not null,
report_type varchar(30) not null comment '报表类型balance, income, cashflow',
period int comment '期间',
order_num int comment '顺序',
itemcode_0 varchar(30) not null comment '报表项目编号',
itemname_0 varchar(255) comment '报表项目名称',
itemtype_0 int default 0 comment '项目计算类型, 0 - 不计算, 1 - 取rule表数据, 2 - 按formula计算',
rownum_0 int comment '行次',
formula_0 varchar(255) comment '计算公式',
balance_0 double comment '金额',
prebalance_0 double comment '金额',
itemcode_1 varchar(30) comment '报表项目编号',
itemname_1 varchar(255) comment '报表项目名称',
itemtype_1 int default 0 comment '项目计算类型, 0 - 不计算, 1 - 取rule表数据, 2 - 按formula计算',
rownum_1 int comment '行次',
formula_1 varchar(255) comment '计算公式',
balance_1 double comment '金额',
prebalance_1 double comment '金额'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='财务报表历史数据';