Skip to content

J
jiedao-api-boot-master
Switch branch/tag
Find file
Normal viewHistoryPermalink
finance.sql 16.5 KB
Newer Older
lanbaoming's avatar
2024-04-08
1397b2a1
 
lanbaoming committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 
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='客户比对表结果表';