{"id":29,"date":"2016-05-19T19:07:14","date_gmt":"2016-05-19T11:07:14","guid":{"rendered":"http:\/\/www.aymoo.com\/?p=29"},"modified":"2016-05-19T19:55:26","modified_gmt":"2016-05-19T11:55:26","slug":"mysql-procedure","status":"publish","type":"post","link":"http:\/\/www.aymoo.com\/?p=29","title":{"rendered":"mysql procedure"},"content":{"rendered":"<pre lang=\"mysql\">\r\nDELIMITER \/\/\r\n\r\nCREATE PROCEDURE fix_trade_status(in var_supplier_trade_id varchar(32))\r\nBEGIN\r\n\r\nDECLARE var_prt_supplier_trade_id varchar(32) DEFAULT '';\r\nDECLARE var_trade_id varchar(32) DEFAULT '';\r\nDECLARE var_prt_trade_id varchar(32) DEFAULT '';\r\n\r\n\r\nSELECT prt_trade_id, trade_id INTO var_prt_supplier_trade_id, var_trade_id FROM supplier_trade WHERE id = var_supplier_trade_id;\r\n\r\nUPDATE 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');\r\n\r\nif var_prt_supplier_trade_id != '' then\r\n    SELECT prt_trade_id INTO var_prt_trade_id FROM prt_supplier_trade WHERE id = var_prt_supplier_trade_id;\r\n\r\n    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');\r\nend if;\r\n\r\nif var_trade_id != '' then\r\n    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');\r\nend if;\r\n\r\n\r\nif var_prt_trade_id != '' then\r\n    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');\r\nend if;\r\n\r\nselect var_prt_supplier_trade_id;\r\n\r\nEND\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>DELIMITER \/\/ CREATE PROCEDURE fix_trade_status(in var_s [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/posts\/29"}],"collection":[{"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=29"}],"version-history":[{"count":3,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions"}],"predecessor-version":[{"id":32,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=\/wp\/v2\/posts\/29\/revisions\/32"}],"wp:attachment":[{"href":"http:\/\/www.aymoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=29"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=29"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.aymoo.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=29"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}