mysql 存储过程拆单

1,表

1.1,订单表
SELECT * FROM `co-sc123`.`order`;

CREATE TABLE `order` (

    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
    `order_no` varchar(60) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '订单号',
    `platform_id` int(11) DEFAULT '0' COMMENT '平台id',
    `channel_id` int(11) unsigned DEFAULT '0' COMMENT '通道id',
    `amount` varchar(32) DEFAULT '0' COMMENT '交易金额 (单位:分)',
    `charges` int(11) unsigned DEFAULT '0' COMMENT '手续费(单位:分)',
    `terminal_rate` int(11) unsigned DEFAULT '0' COMMENT '终端费率(万几)',
    `terminal_rate_fixed_amount` int(11) unsigned DEFAULT '0' COMMENT '终端费率(固定金额:分)',
    `deal_time` datetime DEFAULT NULL COMMENT '交易时间',
    `status` tinyint(3) unsigned DEFAULT '99' COMMENT '交易状态 99成功(暂时只有成功的)',
    `remark` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '备注',
    `create_time` datetime DEFAULT NULL COMMENT '交易时间',
    `count_status` tinyint(2) unsigned DEFAULT '10' COMMENT '统计状态 10未统计 99已统计',
    PRIMARY KEY (`id`) 

) ENGINE=InnoDB AUTO_INCREMENT=77 DEFAULT CHARSET=utf8 COMMENT='订单表'; 


1.2 佣金表
SELECT * FROM `co-sc123`.brokerage;

CREATE TABLE `brokerage` (

    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '佣金记录ID',
    `platform_id` int(11) unsigned DEFAULT '0' COMMENT '平台ID',
    `channel_id` int(11) unsigned DEFAULT '0' COMMENT '通道ID',
    `amount` int(11) unsigned DEFAULT '0' COMMENT '佣金金额(单位:分)',
    `status` tinyint(3) unsigned DEFAULT '10' COMMENT '结算状态 10未结算 20结算失败 80部分成功 99结算成功',
    `create_time` datetime DEFAULT NULL COMMENT '佣金产生时间',
    PRIMARY KEY (`id`) 

) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8 COMMENT='佣金记录表'; 


1.3 代付表
SELECT * FROM `co-sc123`.pay_for_another;

CREATE TABLE `pay_for_another` (

    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '代付ID',
    `platform_id` int(11) unsigned DEFAULT '0' COMMENT '平台ID',
    `channel_id` int(11) unsigned DEFAULT '0' COMMENT '通道ID',
    `to` tinyint(1) unsigned DEFAULT '1' COMMENT '代付给谁 1给co 2给子平台',
    `amount` int(11) unsigned DEFAULT '0' COMMENT '代付金额(单位:分)',
    `status` tinyint(3) unsigned DEFAULT '99' COMMENT '状态 99成功 10待执行 20失败',
    `req_no` varchar(45) DEFAULT NULL COMMENT '代付请求流水,用于代付状态维护',
    `order_id` varchar(45) DEFAULT NULL COMMENT '代付结果查询订单号',
    `create_time` datetime DEFAULT NULL COMMENT '生成时间',
    `exec_time` datetime DEFAULT NULL COMMENT '实际执行时间',
    `exec_time_day` date DEFAULT NULL COMMENT '预计执行时间(天)',
    PRIMARY KEY (`id`) 

) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8 COMMENT='代付表'; 


2,业务逻辑
代付通道最高限额5万,佣金表一条记录对应多条代付记录

3,存储过程
3.1 记录生成 commission_stats

DELIMITER $$
CREATE DEFINER=`bp_user`@`` PROCEDURE `commission_stats`()
BEGIN
declare countt,countsum,idd int(11);
declare yjyj varchar(32);

-- 佣金记录表数据添加
INSERT INTO

    `brokerage`(`platform_id`,`channel_id`,`amount`,`status`,`create_time`)

        SELECT

            `co01`.`platform_id`,
            `co01`.`channel_id`,
            ceil(SUM(`co01`.amount) * (co03.contract_rate_ratio - co02.cost_rate_ratio) / 10000 + COUNT(1) * (co03.contract_rate_fixed_amount - co02.cost_rate_fixed_amount)) yj,
            10,
            SYSDATE() 

        FROM

            `order` co01 

        LEFT JOIN

            `channel` co02 ON `co01`.channel_id = `co02`.id 

        LEFT JOIN

            `platform_channel` co03 ON `co01`.platform_id = `co03`.`platform_id`
            AND `co01`.channel_id = `co03`.channel_id 

        WHERE

            co01.`deal_time` >= DATE_FORMAT(CURDATE() - INTERVAL 1 DAY,
            '%Y-%m-%d %H-%i-%s')
            AND co01.`deal_time` <= DATE_FORMAT(CURDATE(), '%Y-%m-%d %H-%i-%s')
            AND co01.count_status = '10' -- 未统计订单才生成佣金记录 

        GROUP BY `co01`.`platform_id` , `co01`.`channel_id`; 

-- 拆单开始
-- 如果视图存在就删除
DROP VIEW IF EXISTS `platform_for_view`;

-- 创建需拆单代付记录 佣金金额大于通道代付限额
CREATE VIEW platform_for_view AS

    SELECT

        MAX(`co01`.id) id,
        `co01`.`platform_id`,
        `co01`.`channel_id`,
        CONCAT_WS('',
        DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s'),
        FLOOR(RAND() * 100000)) req_no,
        SUM(`co01`.amount) * (co03.contract_rate_ratio - co02.cost_rate_ratio) / 10000 + COUNT(1) * ceil((co03.contract_rate_fixed_amount - co02.cost_rate_fixed_amount)) yj,
        10 status,
        SYSDATE() create_time,
        CURDATE() + INTERVAL SUBSTRING(co02.period, 2) DAY dafutime 

    FROM

        `order` co01 

    LEFT JOIN

        `channel` co02 ON `co01`.channel_id = `co02`.id 

    LEFT JOIN

        `platform_channel` co03 ON `co01`.platform_id = `co03`.`platform_id` 

        AND `co01`.channel_id = `co03`.channel_id 

    WHERE

            co01.`deal_time` >= DATE_FORMAT(CURDATE() - INTERVAL 1 DAY,
            '%Y-%m-%d %H-%i-%s')
            AND co01.`deal_time` <= DATE_FORMAT(CURDATE(), '%Y-%m-%d %H-%i-%s')
            AND co01.count_status = '10' -- 未统计记录才生成代付记录
            AND co02.clearing_form = 2 -- 脚本结算才生成代付记录 

        GROUP BY `co01`.`platform_id` , `co01`.`channel_id`
        HAVING yj > '5000000'; 


-- 计算需循环次数, 每次取一条数据
SELECT

    COUNT(1) num into countsum 

FROM

    platform_for_view; 


SET countt = countsum - 1;

-- 初始化中间表
DROP TABLE IF EXISTS `yj_pay_for_view`;

CREATE TABLE `yj_pay_for_view` (

    `id` int(11) NOT NULL AUTO_INCREMENT,
    `yj` varchar(45) DEFAULT NULL,
    `pay_for_id` int(11) DEFAULT NULL COMMENT '对应代付记录platform_for_view主键',
    PRIMARY KEY (`id`) 

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='佣金拆单中间表(无数据,别删)';

-- 循环从视图中取数据拆单
WHILE countt >= 0 do

    SELECT `id` into idd FROM platform_for_view ORDER BY id asc limit countt,1;
    SELECT `yj` into yjyj FROM platform_for_view WHERE `id` = id ORDER BY id asc limit countt,1;
    CALL split_order(yjyj,'5000000',@moneystr,@cnt);
    SELECT @moneystr,@cnt;
    -- 金额拆开对应到通过idd对应到详细代付信息
    WHILE @cnt > 0 do

            INSERT INTO `yj_pay_for_view`(`yj`,`pay_for_id`) values(SUBSTRING_INDEX(SUBSTRING_INDEX(@moneystr, ',', - @cnt),
            ',',
            1),idd); 

        SET @cnt = @cnt - 1; 

    END WHILE;
    SET countt = countt -1; 


END WHILE;

-- 代付表数据添加 拆单插入
INSERT INTO

    pay_for_another(platform_id,channel_id,req_no,amount,status,create_time,exec_time_day) 

        SELECT

            co02.platform_id,
            co02.channel_id,
            CONCAT_WS('',
            DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s'),
            FLOOR(RAND() * 100000)) req_no,
            CEIL(co01.yj),
            co02.status,
            co02.create_time,
            co02.dafutime 

        FROM

            yj_pay_for_view co01,
            platform_for_view co02 

        WHERE

            co01.pay_for_id = co02.id; 


-- 拆单结束

-- 代付表数据添加 非拆单插入
INSERT INTO
pay_for_another(platform_id,channel_id,req_no,amount,status,create_time,exec_time_day)

    SELECT

        `co01`.`platform_id`,
        `co01`.`channel_id`,
        CONCAT_WS('',
        DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s'),
        FLOOR(RAND() * 100000)) req_no,
        CEIL(SUM(`co01`.amount) * (co03.contract_rate_ratio - co02.cost_rate_ratio) / 10000 + COUNT(1) * (co03.contract_rate_fixed_amount - co02.cost_rate_fixed_amount)) yj,
        10 status,
        SYSDATE() create_time,
        CURDATE() + INTERVAL SUBSTRING(co02.period, 2) DAY dafutime 

    FROM

        `order` co01 

    LEFT JOIN

        `channel` co02 ON `co01`.channel_id = `co02`.id 

    LEFT JOIN

        `platform_channel` co03 ON `co01`.platform_id = `co03`.`platform_id` 

    AND `co01`.channel_id = `co03`.channel_id
    WHERE

        co01.`deal_time` >= DATE_FORMAT(CURDATE() - INTERVAL 1 DAY,
        '%Y-%m-%d %H-%i-%s')
        AND co01.`deal_time` <= DATE_FORMAT(CURDATE(), '%Y-%m-%d %H-%i-%s')
        AND co01.count_status = '10'
        AND co02.clearing_form = 2 

    GROUP BY `co01`.`platform_id` , `co01`.`channel_id`
    HAVING yj <= '5000000' AND yj > 0; 


-- 删除中间表数据
DROP TABLE `yj_pay_for_view`;

-- 订单统计状态维护
SET SQL_SAFE_UPDATES=0;

UPDATE `order` co01 set co01.count_status = '99' WHERE

        co01.`deal_time` >= DATE_FORMAT(CURDATE() - INTERVAL 1 DAY, 

    '%Y-%m-%d %H-%i-%s')
    AND co01.`deal_time` <= DATE_FORMAT(CURDATE(), '%Y-%m-%d %H-%i-%s')
    AND co01.count_status = '10'; -- 修改统计记录 

END$$
DELIMITER ; 


3.2    拆单 split_order

DELIMITER $$
CREATE DEFINER=`bp_user`@`` PROCEDURE `split_order`(IN daifumoney int(11),IN maxmoney int(11),OUT moneystr text,OUT count int(11))
BEGIN
declare money varchar(256);
SET count = 0;
WHILE daifumoney > maxmoney do

    SELECT FLOOR('4000000' + RAND() * (maxmoney - '4000000' + 1)) into money;
    -- 判断金额字符串是否为空
    IF ISNULL(moneystr) || LENGTH(trim(moneystr))<1 THEN

        SELECT concat_ws(',',money) into moneystr; 

    ELSE

        SELECT concat_ws(',',money,moneystr) into moneystr; 

    END IF;
    -- 计数器加1
    SET count = count + 1;
    -- 代付金额减去已进入金额字符串的钱
    SET daifumoney = daifumoney - money;

    IF (daifumoney >0 && daifumoney <= maxmoney) THEN

        -- 判断金额字符串是否为空
        IF ISNULL(moneystr) || LENGTH(trim(moneystr))<1 THEN

            SELECT concat_ws(',',daifumoney) into moneystr; 

        ELSE

            SELECT concat_ws(',',daifumoney,moneystr) into moneystr; 

        END IF; 

    -- 计数器加1
    SET count = count + 1;
    END IF; 


END WHILE;
END$$
DELIMITER ; 


4 调用

call commission_stats();


5 定时调用

5.1 佣金统计每天凌晨30分

30 0 * * *  php1 pathToindex2  pathToAction3 

5.2 代付送单每天9、14、17点的30分钟

30 9,14,17 * * *  php pathToindex  pathToAction

5.3 代付订单状态查询每天10、15、18点的第一分钟

1 10,15,18 * * *  php pathToindex  pathToAction

备注:

1,指定时任务执行的命令(安装路径非默认路径需用which php 找到命令安装路径

2,项目入口文件地址绝对路径

3,方法地址定义如正常访问路由

评论0条