CREATE DEFINER=`root`@`%` PROCEDURE `pro_query_payment_key`(IN `f_pid` varchar(20), IN `paychannel` int, IN `f_money` DECIMAL(10,2), OUT `result` int)
BEGIN
declare i_arrCount int;
declare arrCount int;
declare payment_keys varchar(200);
declare payment_key varchar(20);
declare order_sum decimal(8,2);
declare current_day_remanant_limit decimal(8,2);
set i_arrCount=0;
set arrCount=0;
set payment_keys = '';
set payment_key = '';
set order_sum = 0;
set result = 0;
set current_day_remanant_limit =0;
select keys_id into payment_keys from iweb_payment_key where pid=f_pid and payment_id=paychannel limit 1;
select (select sum(daylimit) from iwb_mer_account_limit where pid=f_pid)-(select ifnull(sum(transMoney),0) from iweb_orders where pid=f_pid and paychannel=paychannel and state=1 and date(payTime)=date(now()))
as remnant_limit into current_day_remanant_limit;
if f_money<=current_day_remanant_limit then
if length(payment_keys)>0 then
select fun_split_total(payment_keys,',') into arrCount;
drop table if exists $tempTable;
create table $tempTable(
pay_key int DEFAULT 0,
orders_sum decimal(8,2) DEFAULT 0
);
while i_arrCount<arrCount do
begin
set i_arrCount=i_arrCount+1;
select fun_split_string(payment_keys,',',i_arrCount) into payment_key;
select ifnull(sum(transMoney),0) as transMoney into order_sum from iweb_orders where pId=f_pid and paychannel=paychannel and date(payTime)=date(now()) and payKey=payment_key and state=1;
insert into $tempTable(pay_key,orders_sum) values(payment_key,order_sum);
end;
end while;
select ifnull(pay_key,0) into result from $tempTable where orders_sum=(select min(orders_sum) from $tempTable limit 1) limit 1;
drop table if exists $tempTable;
end if;
else
select -1 into result;
end if;
end;
call pro_query_payment_key(1000,5,0,@result);
select @result;