93 lines
4.5 KiB
Markdown
93 lines
4.5 KiB
Markdown
# 兰溪存储的流水表的表结构
|
||
|
||
```sql
|
||
CREATE TABLE `ccdi_bank_statement` (
|
||
`bank_statement_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
|
||
`LE_ID` int(10) unsigned DEFAULT '0' COMMENT '企业ID',
|
||
`ACCOUNT_ID` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '账号ID',
|
||
`LE_ACCOUNT_NAME` varchar(240) DEFAULT 'NONE' COMMENT '企业账号名称',
|
||
`LE_ACCOUNT_NO` varchar(240) DEFAULT NULL COMMENT '企业银行账号',
|
||
`ACCOUNTING_DATE_ID` int(11) DEFAULT NULL COMMENT '账号日期ID',
|
||
`ACCOUNTING_DATE` varchar(10) DEFAULT '0000-00-00' COMMENT '账号日期',
|
||
`TRX_DATE` varchar(20) NOT NULL COMMENT '交易日期',
|
||
`CURRENCY` varchar(10) DEFAULT NULL COMMENT '币种',
|
||
`AMOUNT_DR` decimal(19,2) NOT NULL DEFAULT '0.00' COMMENT '付款金额',
|
||
`AMOUNT_CR` decimal(19,2) NOT NULL DEFAULT '0.00' COMMENT '收款金额',
|
||
`AMOUNT_BALANCE` decimal(19,2) NOT NULL COMMENT '余额',
|
||
`CASH_TYPE` varchar(500) DEFAULT NULL COMMENT '交易类型',
|
||
`CUSTOMER_LE_ID` int(11) DEFAULT '-1' COMMENT '对手方企业ID',
|
||
`CUSTOMER_ACCOUNT_NAME` varchar(240) DEFAULT NULL COMMENT '对手方企业名称',
|
||
`CUSTOMER_ACCOUNT_NO` varchar(240) DEFAULT NULL COMMENT '对手方账号',
|
||
`customer_bank` varchar(300) DEFAULT NULL COMMENT '对手方银行',
|
||
`customer_reference` varchar(500) DEFAULT NULL COMMENT '对手方备注',
|
||
`USER_MEMO` varchar(1000) DEFAULT NULL COMMENT '用户交易摘要',
|
||
`BANK_COMMENTS` varchar(240) DEFAULT NULL COMMENT '银行交易摘要',
|
||
`BANK_TRX_NUMBER` varchar(240) DEFAULT NULL COMMENT '银行交易号',
|
||
`BANK` varchar(250) NOT NULL DEFAULT '' COMMENT '所属银行缩写',
|
||
`TRX_FLAG` varchar(2) DEFAULT '0' COMMENT '交易标志位',
|
||
`TRX_TYPE` int(11) NOT NULL DEFAULT '0' COMMENT '分类ID',
|
||
`EXCEPTION_TYPE` varchar(50) NOT NULL DEFAULT '' COMMENT '异常类型',
|
||
`internal_flag` tinyint(1) DEFAULT '0' COMMENT '"是否为内部交易1 是 0 否"',
|
||
`batch_id` int(11) NOT NULL DEFAULT '0' COMMENT '上传logId对应upload_log',
|
||
`batch_sequence` int(11) NOT NULL COMMENT '每次上传在文件中的line',
|
||
`CREATE_DATE` datetime DEFAULT NULL COMMENT '创建时间内',
|
||
`created_by` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '创建者',
|
||
`meta_json` text COMMENT '"meta json"',
|
||
`no_balance` tinyint(1) DEFAULT '0' COMMENT '是否包含余额',
|
||
`begin_balance` tinyint(1) DEFAULT '0' COMMENT '初始余额',
|
||
`end_balance` tinyint(1) DEFAULT '0' COMMENT '结束余额',
|
||
`group_id` int(11) DEFAULT '0' COMMENT '项目id',
|
||
`override_bs_id` bigint(20) DEFAULT '0' COMMENT '=0表示该数据未覆盖主表，>0表示覆盖主表，<0表示被主表覆盖',
|
||
`payment_method` varchar(500) DEFAULT NULL COMMENT '微信、支付宝流水字段，交易方式',
|
||
`cret_no` varchar(20) COMMENT '身份证号',
|
||
PRIMARY KEY (`bank_statement_id`),
|
||
KEY `idx_batch_id_account` (`batch_id`,`LE_ACCOUNT_NO`,`ACCOUNTING_DATE_ID`),
|
||
KEY `GROUP_ID` (`group_id`),
|
||
KEY `c4c_bank_statement_stg_batch_id_IDX` (`batch_id`,`LE_ACCOUNT_NO`) USING BTREE
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='银行流水的中间处理表';
|
||
```
|
||
|
||
流水表和返回值的对应关系
|
||
|
||
| 序号 | ccdi_bank_statement | 返回值 |
|
||
| --- | --- | --- |
|
||
| 1 | bank_statement_id | bankStatementId |
|
||
| 2 | LE_ID | leId |
|
||
| 3 | ACCOUNT_ID | accountId |
|
||
| 4 | LE_ACCOUNT_NAME | leName |
|
||
| 5 | LE_ACCOUNT_NO | accountNo |
|
||
| 6 | ACCOUNTING_DATE_ID | accountingDateId |
|
||
| 7 | ACCOUNTING_DATE | accountingDate |
|
||
| 8 | TRX_DATE | trxDate |
|
||
| 9 | CURRENCY | currency |
|
||
| 10 | AMOUNT_DR | drAmount |
|
||
| 11 | AMOUNT_CR | crAmount |
|
||
| 12 | AMOUNT_BALANCE | balanceAmount |
|
||
| 13 | CASH_TYPE | cashType |
|
||
| 14 | CUSTOMER_LE_ID | customerId |
|
||
| 15 | CUSTOMER_ACCOUNT_NAME | customerName |
|
||
| 16 | CUSTOMER_ACCOUNT_NO | customerAccountNo |
|
||
| 17 | customer_bank | customerBank |
|
||
| 18 | customer_reference | customerReference |
|
||
| 19 | USER_MEMO | userMemo |
|
||
| 20 | BANK_COMMENTS | bankComments |
|
||
| 21 | BANK_TRX_NUMBER | bankTrxNumber |
|
||
| 22 | BANK | bank |
|
||
| 23 | TRX_FLAG | transFlag |
|
||
| 24 | TRX_TYPE | transTypeId |
|
||
| 25 | EXCEPTION_TYPE | exceptionType |
|
||
| 26 | internal_flag | internalFlag |
|
||
| 27 | batch_id | batchId |
|
||
| 28 | batch_sequence | uploadSequnceNumber |
|
||
| 29 | CREATE_DATE | createDate |
|
||
| 30 | created_by | createdBy |
|
||
| 31 | meta_json | 设置为null |
|
||
| 32 | no_balance | isNoBalance |
|
||
| 33 | begin_balance | isBeginBalance |
|
||
| 34 | end_balance | isEndBalance |
|
||
| 35 | override_bs_id | overrideBsId |
|
||
| 36 | payment_method | paymentMethod |
|
||
| 37 | cret_no | cretNo |
|
||
| 38 | group_id | groupId |
|
||
|