Whistle

DELIMITER //
 
CREATE PROCEDURE fix_trade_status(in var_supplier_trade_id varchar(32))
BEGIN
 
DECLARE var_prt_supplier_trade_id varchar(32) DEFAULT '';
DECLARE var_trade_id varchar(32) DEFAULT '';
DECLARE var_prt_trade_id varchar(32) DEFAULT '';
 
 
SELECT prt_trade_id, trade_id INTO var_prt_supplier_trade_id, var_trade_id FROM supplier_trade WHERE id = var_supplier_trade_id;
 
UPDATE supplier_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade_item WHERE trade_id = var_supplier_trade_id AND refund_status = 'refunded');
 
if var_prt_supplier_trade_id != '' then
    SELECT prt_trade_id INTO var_prt_trade_id FROM prt_supplier_trade WHERE id = var_prt_supplier_trade_id;
 
    UPDATE prt_supplier_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_prt_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade WHERE prt_trade_id = var_prt_supplier_trade_id AND status = 'closed');
end if;
 
if var_trade_id != '' then
    UPDATE trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM supplier_trade_item WHERE trade_id = var_supplier_trade_id AND refund_status = 'refunded');
end if;
 
 
if var_prt_trade_id != '' then
    UPDATE prt_trade SET status = 'closed', finished_type = 'refunded', fulfillment_status = 'finished', refunded_at = unix_timestamp(), closed_at = unix_timestamp(), success_at = unix_timestamp() WHERE id = var_prt_supplier_trade_id AND has_refund = 1 AND order_number = (SELECT COUNT(id) FROM trade WHERE prt_trade_id = var_prt_supplier_trade_id AND status = 'closed');
end if;
 
select var_prt_supplier_trade_id;
 
END