php自动按天清空库存,swoole+PHP实现自动取消订单，还原库存等操作

一、业务场景：当客户下单在指定的时间内如果没有付款，那我们需要将这笔订单取消掉，比如好的处理方法是运用延时取消，这里我们用到了swoole，运用swoole的异步毫秒定时器不会影响到当前程序的运行，具体参考：https://wiki.swoole.com/wiki/page/319.html

二、说明，order_status为1时代表客户下单确定，为2时代表客户已付款，为0时代表订单已取消(正是swoole来做的)，下面的代表我没有用框架，比较纯的PHP代表方便理解和应用

三、举例说明，库存表csdn_product_stock产品ID为1的产品库存数量为20，产品ID为2的库存数量为40，然后客户下单一笔产品ID1减10，产品ID2减20，所以库存表只够2次下单，例子中10秒后自动还原库存，如下图：

图解：1、第一次下完单产品ID1库存从20减到了10，产品ID2库存从40减到了20；2、第二次下完单产品ID的库存为0了，产品ID2的库存也为0了，3、第三次下单时，程序提示Out of stock；4、过了10秒钟(每个订单下单后往后推10秒)，客户两次下单，由于没有付款(csdn_order表的order_status为1)，产品1和产品2的库存被还原了(csdn_order表的order_status变为0)，客户又可以继续下单了

1、所需要sql数据库表

DROP TABLE IF EXISTS `csdn_order`;

CREATE TABLE `csdn_order` (

`order_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`order_amount` float(10,2) unsigned NOT NULL DEFAULT "0.00",

`user_name` varchar(64) CHARACTER SET latin1 NOT NULL DEFAULT "",

`order_status` tinyint(2) unsigned NOT NULL DEFAULT "0",

`date_created` datetime NOT NULL,

PRIMARY KEY (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `csdn_order_detail`;

CREATE TABLE `csdn_order_detail` (

`detail_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`order_id` int(10) unsigned NOT NULL,

`product_id` int(10) NOT NULL,

`product_price` float(10,2) NOT NULL,

`product_number` smallint(4) unsigned NOT NULL DEFAULT "0",

`date_created` datetime NOT NULL,

PRIMARY KEY (`detail_id`),

KEY `idx_order_id` (`order_id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `csdn_product_stock`;

CREATE TABLE `csdn_product_stock` (

`auto_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`product_id` int(10) NOT NULL,

`product_stock_number` int(10) unsigned NOT NULL,

`date_modified` datetime NOT NULL,

PRIMARY KEY (`auto_id`),

KEY `idx_product_id` (`product_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO `csdn_product_stock` VALUES ("1", "1", "20", "2018-09-13 19:36:19");

INSERT INTO `csdn_product_stock` VALUES ("2", "2", "40", "2018-09-13 19:36:19");

2、数据库配置信息：config.php

$dbHost = "192.168.0.110";

$dbUser = "root";

$dbPassword = "123456";

$dbName = "test123";

?>

3、order_submit.php，生成订单

require("config.php");

try {

$pdo = new PDO("mysql:host=" . $dbHost . ";dbname=" . $dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT => true));

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 1);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$orderInfo = array(

"order_amount" => 10.92,

"user_name" => "yusan",

"order_status" => 1,

"date_created" => "now()",

"product_lit" => array(

0 => array(

"product_id" => 1,

"product_price" => 5.00,

"product_number" => 10,

"date_created" => "now()"

),

1 => array(

"product_id" => 2,

"product_price" => 5.92,

"product_number" => 20,

"date_created" => "now()"

)

)

);

try{

$pdo->beginTransaction();//开启事务处理

$sql = "insert into csdn_order (order_amount, user_name, order_status, date_created) values (:orderAmount, :userName, :orderStatus, now())";

$stmt = $pdo->prepare($sql);

$affectedRows = $stmt->execute(array(":orderAmount" => $orderInfo["order_amount"], ":userName" => $orderInfo["user_name"], ":orderStatus" => $orderInfo["order_status"]));

$orderId = $pdo->lastInsertId();

if(!$affectedRows) {

throw new PDOException("Failure to submit order!");

}

foreach($orderInfo["product_lit"] as $productInfo) {

$sqlProductDetail = "insert into csdn_order_detail (order_id, product_id, product_price, product_number, date_created) values (:orderId, :productId, :productPrice, :productNumber, now())";

$stmtProductDetail = $pdo->prepare($sqlProductDetail);

$stmtProductDetail->execute(array(":orderId" => $orderId, ":productId" => $productInfo["product_id"], ":productPrice" => $productInfo["product_price"], ":productNumber" => $productInfo["product_number"]));

$sqlCheck = "select product_stock_number from csdn_product_stock where product_id=:productId";

$stmtCheck = $pdo->prepare($sqlCheck);

$stmtCheck->execute(array(":productId" => $productInfo["product_id"]));

$rowCheck = $stmtCheck->fetch(PDO::FETCH_ASSOC);

if($rowCheck["product_stock_number"] < $productInfo["product_number"]) {

throw new PDOException("Out of stock, Failure to submit order!");

}

$sqlProductStock = "update csdn_product_stock set product_stock_number=product_stock_number-:productNumber, date_modified=now() where product_id=:productId";

$stmtProductStock = $pdo->prepare($sqlProductStock);

$stmtProductStock->execute(array(":productNumber" => $productInfo["product_number"], ":productId" => $productInfo["product_id"]));

$affectedRowsProductStock = $stmtProductStock->rowCount();

//库存没有正常扣除，失败，库存表里的product_stock_number设置了为非负数 //如果库存不足时，sql异常：SQLSTATE[22003]: Numeric value out of range: 1690 BIGINT UNSIGNED value is out of range in "(`test`.`csdn_product_stock`.`product_stock_number` - 20)" if($affectedRowsProductStock <= 0) {

throw new PDOException("Out of stock, Failure to submit order!");

}

}

echo "Successful, Order Id is：" . $orderId ."，Order Amount is：" . $orderInfo["order_amount"] . "。";

$pdo->commit();//提交事务 //exec("php order_cancel.php -a" . $orderId . " &"); pclose(popen("php order_cancel.php -a " . $orderId . " &", "w"));

//system("php order_cancel.php -a" . $orderId . " &", $phpResult); //echo $phpResult; }catch(PDOException $e){

echo $e->getMessage();

$pdo->rollback();

}

$pdo = null;

} catch (PDOException $e) {

echo $e->getMessage();

}

?>

4、order_cancel.php，这个方法主要就是做订单自动取消，并还原库存的业务处理

require("config.php");

$queryString = getopt("a:");

$userParams = array($queryString);

appendLog(date("Y-m-d H:i:s") . "" . $queryString["a"] . "" . "start");

try {

$pdo = new PDO("mysql:host=" . $dbHost . ";dbname=" . $dbName, $dbUser, $dbPassword, array(PDO::ATTR_PERSISTENT => true));

$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT, 0);

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

swoole_timer_after(10000, function ($queryString) {

global $queryString, $pdo;

try{

$pdo->beginTransaction();//开启事务处理

$orderId = $queryString["a"];

$sql = "select order_status from csdn_order where order_id=:orderId";

$stmt = $pdo->prepare($sql);

$stmt->execute(array(":orderId" => $orderId));

$row = $stmt->fetch(PDO::FETCH_ASSOC);

//$row["order_status"] === "1"代表已下单，但未付款，我们还原库存只针对未付款的订单 if(isset($row["order_status"]) && $row["order_status"] === "1") {

$sqlOrderDetail = "select product_id, product_number from csdn_order_detail where order_id=:orderId";

$stmtOrderDetail = $pdo->prepare($sqlOrderDetail);

$stmtOrderDetail->execute(array(":orderId" => $orderId));

while($rowOrderDetail = $stmtOrderDetail->fetch(PDO::FETCH_ASSOC)) {

$sqlRestoreStock = "update csdn_product_stock set product_stock_number=product_stock_number + :productNumber, date_modified=now() where product_id=:productId";

$stmtRestoreStock = $pdo->prepare($sqlRestoreStock);

$stmtRestoreStock->execute(array(":productNumber" => $rowOrderDetail["product_number"], ":productId" => $rowOrderDetail["product_id"]));

}

$sqlRestoreOrder = "update csdn_order set order_status=:orderStatus where order_id=:orderId";

$stmtRestoreOrder = $pdo->prepare($sqlRestoreOrder);

$stmtRestoreOrder->execute(array(":orderStatus" => 0, ":orderId" => $orderId));

}

$pdo->commit();//提交事务 }catch(PDOException $e){

echo $e->getMessage();

$pdo->rollback();

}

$pdo = null;

appendLog(date("Y-m-d H:i:s") . "" . $queryString["a"] . "" . "end" . json_encode($queryString));

}, $pdo);

} catch (PDOException $e) {

echo $e->getMessage();

}

function appendLog($str) {

$dir = "log.txt";

$fh = fopen($dir, "a");

fwrite($fh, $str . "");

fclose($fh);

}

?>