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='客户比对表结果表';


