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条