1.数据库

1.1字段添加与初始化

#电商
ALTER TABLE mall_trade.invoice_apply_record ADD apply_result CHAR(1) NULL 
COMMENT '申请结果：1:等待中 2:申请失败 3:申请成功';
#全员营销
ALTER TABLE im.invoice_apply_record ADD apply_result CHAR(1) NULL 
COMMENT '申请结果：1:等待中 2:申请失败 3:申请成功';

#所有已开票和已邮寄的发票申请 均是申请成功
update im.invoice_apply_record 
set apply_result = '3' 
where `status` in ('2','3'); 
#所有纸质（专用）发票申请未开票的 均是等待中
update im.invoice_apply_record
set apply_result ='1'
where `status` = '1' and invoice_type = '2';
#所有电票申请  未开票的 均为开票失败
update im.invoice_apply_record
set apply_result = '2'
where  `status`='1' and invoice_type='1';

1.2yurun_common数据库新增数据表

use yurun_common;
CREATE TABLE invoice_record(
    id INT NOT NULL AUTO_INCREMENT  COMMENT 'id' ,
    invoice_from VARCHAR(32)    COMMENT '开票来源 1-电商2-全员营销3-X6' ,
    invoice_type CHAR(1)    COMMENT '发票类型 1-电子普票2-纸质普票' ,
    apply_id INT    COMMENT '开票申请id' ,
    order_id VARCHAR(32)    COMMENT '开票订单id' ,
    invoice_serial_no VARCHAR(32)    COMMENT '发票流水号' ,
    invoice_no VARCHAR(32)    COMMENT '发票号码' ,
    invoice_code VARCHAR(128)    COMMENT '发票代码' ,
    total_amount_tax DECIMAL(10,2)    COMMENT '价税合计金额' ,
    total_amount_notax DECIMAL(10,2)    COMMENT '合计不含税金额' ,
    total_tax DECIMAL(10,2)    COMMENT '合计税额' ,
    original_invoice_no VARCHAR(128)    COMMENT '原发票票号' ,
    original_invoice_code VARCHAR(128)    COMMENT '原发票代码' ,
    invoice_url VARCHAR(1024)    COMMENT '发票url' ,
    invoice_status CHAR(1)    COMMENT '发票状态 1-未开票2-已开票3-已下载4-已红冲' ,
    is_red CHAR(1)    COMMENT '是否红冲 0-否1-是' ,
    invoice_config_id INT    COMMENT '发票配置编号' ,
    invoice_time DATETIME    COMMENT '开票日期' ,
    PRIMARY KEY (id)
) COMMENT = '发票信息表 ';
CREATE TABLE invoice_detail(
    id INT NOT NULL AUTO_INCREMENT  COMMENT 'id' ,
    invoice_id INT    COMMENT '发票id' ,
    price DECIMAL(10,2)    COMMENT '价格' ,
    num INT    COMMENT '数量' ,
    amount DECIMAL(10,2)    COMMENT '合计' ,
    tax_amount DECIMAL(10,2)    COMMENT '合计税额' ,
    tax_rate DECIMAL(10,2)    COMMENT '合计税率' ,
    order_detail_id INT    COMMENT '订单明细编号' ,
    PRIMARY KEY (id)
) COMMENT = '发票明细表 ';
CREATE TABLE invoice_config(
    id INT NOT NULL AUTO_INCREMENT  COMMENT 'id' ,
    user_name VARCHAR(64)    COMMENT '平台用户名' ,
    password VARCHAR(64)    COMMENT '平台密码' ,
    taxpayer_id VARCHAR(64)    COMMENT '纳税人识别号' ,
    authorization_code VARCHAR(32)    COMMENT '接入系统平台授权码' ,
    qymc VARCHAR(128)    COMMENT '开票方名称' ,
    nsrdzdah VARCHAR(32)    COMMENT '开票方电子档案号' ,
    swjg_dm VARCHAR(32)    COMMENT '税务机构代码' ,
    bmb_bbh VARCHAR(32)    COMMENT '编码表版本号' ,
    xhf_dz VARCHAR(128)    COMMENT '销货方地址' ,
    xhf_dh VARCHAR(32)    COMMENT '销货方电话' ,
    xhf_yhzh VARCHAR(128)    COMMENT '销货方银行账号' ,
    version VARCHAR(32)    COMMENT '接口版本号' ,
    max_bill_amount DECIMAL(10,2)    COMMENT '开票面额' ,
    issuer_name VARCHAR(32)    COMMENT '开票人姓名' ,
    payee_name VARCHAR(32)    COMMENT '收款人姓名' ,
    reviewer_name VARCHAR(32)    COMMENT '复核人姓名' ,
    PRIMARY KEY (id)
) COMMENT = '发票主体配置表 ';
CREATE TABLE tax_classification_code(
    id INT NOT NULL AUTO_INCREMENT  COMMENT 'id' ,
    code VARCHAR(32)    COMMENT '编码' ,
    classification_code VARCHAR(32)    COMMENT '分类编码' ,
    name VARCHAR(128)    COMMENT '分类名称' ,
    abbreviation VARCHAR(32)    COMMENT '分类简称' ,
    tax_rate VARCHAR(32)    COMMENT '税率' ,
    special_management VARCHAR(32)    COMMENT '增值税特殊管理' ,
    PRIMARY KEY (id)
) COMMENT = '税收分类编码表';

1.3表数据初始化

发票相关表

INSERT INTO `yurun_common`.`invoice_config`(`id`, `user_name`, `password`, `taxpayer_id`, `authorization_code`, `qymc`, `nsrdzdah`, `swjg_dm`, `bmb_bbh`, `xhf_dz`, `xhf_dh`, `xhf_yhzh`, `version`, `max_bill_amount`, `issuer_name`, `payee_name`, `reviewer_name`) VALUES (1, 'xaJ8DlpY', '3201000130NDgxYjE4N2RiODA4Yjk3MQ==', '91320105067086195W', 'XFYE6BJ24H', '南京雨润连锁经营管理有限公司', NULL, NULL, '18.0', '江苏南京浦口区桥林紫峰路19号', '025-66200131', '中国农业银行南京汉中西路支行 10107001040219131', '2.0', 100000.00, '熊钰馨', '吕小娣', '孔祥晨');

insert into yurun_common.invoice_record 
(id,apply_id,invoice_from,invoice_type,order_id,invoice_serial_no,invoice_no,invoice_code,
 total_amount_tax,total_amount_notax,total_tax,invoice_url,invoice_status,invoice_time,is_red,
 original_invoice_no,original_invoice_code,invoice_config_id)
select
id,apply_id,'2',IF(invoice_type = '2','1','2') ,order_id,invoice_serial_no,invoice_no,invoice_code,total_amount_tax,total_amount_notax,
total_tax,invoice_url, IF(invoice_status = '4','3',invoice_status),create_time,is_red,original_invoice_no,original_invoice_code,1
from 
im.invoice_no_record;

insert into 
yurun_common.invoice_detail
(id,invoice_id,price,num,amount,tax_amount,tax_rate,order_detail_id)
select 
id,invoice_id,price,num,amount,tax_amount,tax_rate,order_detail_id
from im.invoice_item_detail;

税收分类编码表
见初始化文件

2.Nacos配置文件

2.1新增服务配置文件（invoice-service.yaml）

# 数据源配置
spring:
  datasource:
    url: xxxxx：xxx
    username: xxxx
    password: xxxx
invoice:
  url: "http://open.jsaisino.com/dianzifapiaoService/slqzconsole.do"
rocketmq:
  nameServer: xxxxx
  producer:
    #超时时间
    sendMessageTimeout: 30000
    #发送组
    group: publicInvoiceGroup
  topic:
    invoiceApply: invoiceApply_pro
    invoiceDownload: invoiceDownload_pro
    invoiceResult: invoiceResult_pro
    invoiceApplyRed: invoiceApplyRed_pro
    invoiceResultRed: invoiceResultRed_pro
  consumerGroup:
    invoiceApply: invoiceApply_pro
    invoiceDownload: invoiceDownload_pro
    invoiceApplyRed: invoiceApplyRed_pro

2.2全员营销配置项(im-service.yaml)

在原有配置基础上添加如下配置

#添加队列相关配置
rocketmq:
  nameServer: xxxxxx
  producer:
    #超时时间
    sendMessageTimeout: 30000
    #发送组
    group: biGroup
  topic:
    invoiceApply: invoiceApply_pro
    invoiceResult: invoiceResult_pro
  consumerGroup:
    invoiceResult: invoiceResult_im_pro

2.3电商配置项（mall-trade-service.yaml）

在原有的队列配置中添加几个topic和消费者组

# 队列配置
rocketmq:
  nameServer: xxxxxxxxx
  producer:
    #超时时间
    sendMessageTimeout: 30000
    #发送组
    group: mallGroup
  topic:
    autoCancel: mall_autoCancel
    autoComplete: mall_autoComplete
    autoCloseReturn: mall_autoCloseReturn
    invoiceApply: invoiceApply_pro
    invoiceResult: invoiceResult_pro
    invoiceResultRed: invoiceResultRed_pro
    invoiceApplyRed: invoiceApplyRed_pro
  consumerGroup:
    autoCancel: mall_autoCancel
    autoComplete: mall_autoComplete
    autoCloseReturn: mall_autoCloseReturn
    invoiceResult: invoiceResult_trade_pro
    invoiceResultRed: invoiceResultRed_trade_pro

2.4文档中心和网关的路由配置项

- id: invoice-service
  uri: lb://invoice-service
  predicates:
    - Path=/invoice/**
  filters:
    - StripPrefix=1

3.生产RocketMQ创建话题

队列作用	生产
正票开具申请队列	invoiceApply_pro
红票开具申请队列	invoiceApplyRed_pro
正票结果队列	invoiceResult_pro
红票结果队列	invoiceResultRed_pro
下载队列	invoiceDownload_pro

雨润工作笔记

正式环境发票升级脚本

1.数据库

1.1字段添加与初始化

1.2yurun_common数据库新增数据表

1.3表数据初始化

2.Nacos配置文件

2.1新增服务配置文件（invoice-service.yaml）

2.2全员营销配置项(im-service.yaml)

2.3电商配置项（mall-trade-service.yaml）

2.4文档中心和网关的路由配置项

3.生产RocketMQ创建话题