INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`,
`creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (8, '财务审核', '8', 'bpm_model_category', 0, 'default', '', NULL, '1', '2022-12-11 20:48:45', '1',
'2023-01-15 18:29:50', b'0', 'finance approva');
DROP TABLE IF EXISTS `ecw_receipt_approval`;
CREATE TABLE `ecw_receipt_approval`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`receipt_id` bigint NOT NULL COMMENT '收款单ID',
`receipt_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '收款单编号',
`receipt_item_id` bigint DEFAULT NULL COMMENT '收款单ID',
`bmp_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程表单实例ID',
`bmp_key` varchar(100) NOT NULL COMMENT '流程实例KEY类型参考:WorkFlowEmus',
`status` tinyint DEFAULT '1' COMMENT '状态：1 处理中 2 通过 3 不通过 4 取消',
`reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请原因',
`processing_results` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '处理结果',
`creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`deleted` bit(1) DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='收款单或者收款明细审核表';
alter table ecw_receipt add column `tax_point` decimal(10,2) DEFAULT NULL COMMENT '税点';
alter table ecw_receipt_item add column `tax_point` decimal(10,2) DEFAULT '0.00' COMMENT '税点';
alter table ecw_receipt_item add column `tax_amount` decimal(15,2) DEFAULT '0.00' COMMENT '含税金额';
alter table ecw_receivable add column `tax_point` decimal(10,2) DEFAULT '0.00' COMMENT '税点';
alter table ecw_receivable add column `tax_amount` decimal(15,2) DEFAULT '0.00' COMMENT '含税金额';
alter table ecw_receipt add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
alter table ecw_receipt_item add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
DROP TABLE IF EXISTS `ecw_payment_approval`;
CREATE TABLE `ecw_payment_approval`
(
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`payment_id` bigint NOT NULL COMMENT '付款单ID',
`payment_no` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '付款单编号',
`bmp_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '流程表单实例ID',
`bmp_key` varchar(100) NOT NULL COMMENT '流程实例KEY类型参考:WorkFlowEmus',
`status` tinyint DEFAULT '1' COMMENT '状态：1 处理中 2 通过 3 不通过 4 取消',
`reason` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '申请原因',
`processing_results` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '处理结果',
`creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime NOT NULL COMMENT '创建时间',
`updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`deleted` bit(1) DEFAULT b'0' COMMENT '是否删除',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='付款单审核表';
alter table ecw_payment add column `bmp_status` tinyint DEFAULT NULL COMMENT '流程状态：1 处理中 2 通过 3 不通过 4 取消';
alter table ecw_receipt add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
alter table ecw_receipt_item add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
alter table ecw_payment add column `bmp_id` varchar(100) DEFAULT NULL COMMENT '当前流程ID';
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 7, '审核中', '7', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 8, '审核失败', '8', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_FAIL');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 9, '反审核中', '9', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'APPROVE_NEGATION');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 10, '全部核销中', '10', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'WRITE_OFF_ALL_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES ( 11, '反核销审核中', '11', 'receipt_state', 0, 'default', '', NULL, '1', '2022-08-31 10:24:39', '115', '2023-01-15 18:09:20', b'0', 'WRITE_OFF_ALL_NEGATION');
delete from system_dict_data t where t.dict_type='payment_state';
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (1, '草稿', '0', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'DRAFT');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (2, '待审核', '1', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (3, '审批驳回', '2', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_FAIL');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (4, '反审核中', '3', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'APPROVE_NEGATION');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (5, '待核销', '4', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_WAITING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (6, '核销审核中', '5', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (7, '已核销', '6', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (8, '反核销审核中', '7', 'payment_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES
('收款明细状态', 'receipt_item_state', 0, '状态: 0待核销，1已核销，2核销审核中，3反核销审核中', '1', '2022-07-11 21:20:46', '1', '2022-07-11 21:20:46', b'0');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (1, '待核销', '0', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (2, '已核销', '1', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (3, '核销审核中', '2', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
INSERT INTO `system_dict_data`(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES (4, '反核销审核中', '3', 'receipt_item_state', 0, 'default', '', NULL, '1', '2022-08-18 10:11:57', '1', '2023-01-15 18:09:17', b'0', 'WRITE_OFF_NO_APPROVE_ING');
-- 对比空运客户
CREATE DEFINER = `root` @`%` PROCEDURE `compare_air_customer` ()
BEGIN
DECLARE s INT DEFAULT 0;
DECLARE cname VARCHAR ( 100 );
DECLARE cphone VARCHAR ( 100 );
DECLARE ccustomer VARCHAR ( 100 );-- 定义游标，并将sql结果集赋值到游标中，report为游标名
DECLARE report CURSOR FOR SELECT t.NAME, replace( concat(t.area_code,t.area_phone),'+',''),t.customer_name FROM tmp_air_customer t;-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;-- 打开游标
OPEN report;-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cname,cphone,ccustomer;-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE s <> 1 DO
IF cphone <> '' AND ccustomer <> '' THEN
SET @new_count = ( SELECT count( 1 ) FROM ecw_customer_contacts s WHERE s.deleted = 0 and concat_ws( '', s.area_code, s.phone_new ) = cphone );
IF @new_count = 0 THEN
INSERT INTO `compare_air_customer_result` ( `name`, `phone`, `customer_name`, `type` )VALUES( cname, cphone, ccustomer, '1' );
ELSE
SET @customer_id = ( SELECT customer_id FROM ecw_customer_contacts s WHERE s.deleted = 0 and concat_ws( '', s.area_code, s.phone_new ) = cphone LIMIT 1 );
SET @tphone=cphone;
SET @tcustomer=ccustomer;
IF ifnull(@customer_id,0) > 0 THEN
SET @customer_service_name = (SELECT s.username FROM system_user s WHERE s.id =(SELECT customer_service FROM ecw_customer WHERE id = @customer_id ));
IF LOCATE( @customer_service_name, ccustomer ) > 0 THEN
INSERT INTO `compare_air_customer_result` ( `number`, `name`, `phone`, `old_customer_name`, `type` )
SELECT number,NAME,@tphone,@tcustomer,'2' FROM ecw_customer WHERE id = @customer_id;
ELSE
SET @is_open_sea = ( SELECT is_in_open_sea FROM ecw_customer WHERE id = @customer_id );
IF @is_open_sea = 1 THEN
INSERT INTO compare_air_customer_result ( `number`, `name`, `phone`, `old_customer_name`, `customer_name`, `enter_open_sea_time`, `creator`, `create_time`, `type` )
SELECT number,NAME,@tphone,@tcustomer,@customer_service_name,enter_open_sea_time,creator,create_time,'4' FROM ecw_customer WHERE id = @customer_id;
ELSE
INSERT INTO compare_air_customer_result ( `number`, `name`, `phone`, `old_customer_name`, `customer_name`, `creator`, `create_time`, `type` )
SELECT number,NAME,@tphone,@tcustomer,@customer_service_name,creator,create_time,'3' FROM ecw_customer WHERE id = @customer_id;
END IF;
END IF;
END IF;
END IF;
END IF;
FETCH report INTO cname,cphone,ccustomer;
END WHILE;
CLOSE report;
END
drop table if exists `tmp_air_customer`;
CREATE TABLE `tmp_air_customer` (
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'phone',
`customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customer_name',
`area_code` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'area_code',
`area_phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'area_phone',
`customer_id` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customer_id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='同步空运客户表';
drop table if exists `compare_air_customer_result`;
CREATE TABLE `compare_air_customer_result` (
`id` bigint NOT NULL AUTO_INCREMENT,
`number` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'name',
`phone` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'phone',
`old_customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'old_customerName',
`customer_name` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'customerName',
`enter_open_sea_time` datetime default null COMMENT '入公海时间',
`creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
`create_time` datetime default null COMMENT '创建时间',
`type` varchar(1) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'type',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='客户比对表结果表';