use Domain\Asset\Register\UseCase\CreateAsset;
require_once __DIR__ . '/../../../../functions/nestedset_assets.php';
$post = @file_get_contents('php://input'); //获取请求体，@的作用为屏蔽警告，可去除。
$post = json_decode($post, true);
if (!isset($post[0]['criticality_code'])) {
$post[0]['criticality_code'] = 0;
}
if (!isset($post[0]['function_code'])) {
$post[0]['function_code'] = 0;
}
if (!isset($post[0]['asset_ff_1'])) {
$post[0]['asset_ff_1'] = '';
}
// 直接进行添加，没有层级区分
if ($post[0]['asset_code'] == $post[0]['asset_primary_code']) {
//criticality_code 换取id
$criticality_query = $DB->prepare("
SELECT criticality_id
FROM mic_criticality
WHERE criticality_code = :criticality_code
");
$criticality_query->bindvalue(':criticality_code', $post[0]['criticality_code']);
if (!$criticality_query->execute()) {
Flight::error(new Exception(errorInfo($criticality_query)));
}
$items = [];
$line = $criticality_query->fetch(PDO::FETCH_OBJ);
$criticality_id = $line->criticality_id;
//function_code 换取id
$function_query = $DB->prepare("
SELECT function_id
FROM mic_function
WHERE function_code = :function_code
");
$function_query->bindvalue(':function_code', $post[0]['function_code']);
if (!$function_query->execute()) {
Flight::error(new Exception(errorInfo($function_query)));
}
$items = [];
$line = $function_query->fetch(PDO::FETCH_OBJ);
$function_id = $line->function_id;
// 用location_code 换取id,
$query = $DB->prepare("
SELECT location_id
FROM asset_location
WHERE location_code = :location_code
");
$query->bindvalue(':location_code', $post[0]['location_code']);
if (!$query->execute()) {
Flight::error(new Exception(errorInfo($query)));
}
$items = [];
$line = $query->fetch(PDO::FETCH_OBJ);
$location_id = $line->location_id;
// 大类code换去大类id
$category_query = $DB->prepare("
SELECT asset_category_id
FROM mic_asset_category
WHERE asset_category_code = :asset_category_code
");
$category_query->bindvalue(':asset_category_code', $post[0]['asset_category_code']);
if (!$category_query->execute()) {
Flight::error(new Exception(errorInfo($category_query)));
}
$line = $category_query->fetch(PDO::FETCH_OBJ);
$asset_category_id = $line->asset_category_id;
// 根据供应商code 获取供应商ID
$pur_supplier_query = $DB->prepare("
SELECT supplier_id
FROM pur_supplier
WHERE supplier_code = :supplier_code
");
$pur_supplier_query->bindvalue(':supplier_code', $post[0]['supplier_code']);
if (!$pur_supplier_query->execute()) {
Flight::error(new Exception(errorInfo($pur_supplier_query)));
}
$line = $pur_supplier_query->fetch(PDO::FETCH_OBJ);
$supplier_id = $line->supplier_id;
$object = new CreateAsset($DB);
$params =
[
'asset_name' => $post[0]['asset_name'],
'asset_code' => $post[0]['asset_code'],
'asset_nature' => $post[0]['asset_nature'],
'location_id' => $location_id,
'asset_status' => $post[0]['asset_status'],
'asset_model' => $post[0]['asset_model'],
'asset_category_id' => $asset_category_id,
'supplier_id' => $supplier_id,
'asset_description' => $post[0]['asset_description'],
'asset_acquisition_price' => $post[0]['asset_acquisition_price'],
'asset_service_start_date' => $post[0]['asset_service_start_date'],
'asset_ff_1' => $post[0]['asset_ff_1'],
'asset_ff_2' => $post[0]['asset_ff_2'],
'function_id' => $function_id,
'criticality_id' => $criticality_id,
'asset_primary_code' => $post[0]['asset_primary_code'],
];
$result_son = $object->execute($params);
update_log($result_son);
} else {
//criticality_code 换取id
$criticality_query = $DB->prepare("
SELECT criticality_id
FROM mic_criticality
WHERE criticality_code = :criticality_code
");
$criticality_query->bindvalue(':criticality_code', $post[0]['criticality_code']);
if (!$criticality_query->execute()) {
Flight::error(new Exception(errorInfo($criticality_query)));
}
$items = [];
$line = $criticality_query->fetch(PDO::FETCH_OBJ);
$criticality_id = $line->criticality_id;
//function_code 换取id
$function_query = $DB->prepare("
SELECT function_id
FROM mic_function
WHERE function_code = :function_code
");
$function_query->bindvalue(':function_code', $post[0]['function_code']);
if (!$function_query->execute()) {
Flight::error(new Exception(errorInfo($function_query)));
}
$items = [];
$line = $function_query->fetch(PDO::FETCH_OBJ);
$function_id = $line->function_id;
// 用location_code 换取id,
$query = $DB->prepare("
SELECT location_id
FROM asset_location
WHERE location_code = :location_code
");
$query->bindvalue(':location_code', $post[0]['location_code']);
if (!$query->execute()) {
Flight::error(new Exception(errorInfo($query)));
}
$items = [];
$line = $query->fetch(PDO::FETCH_OBJ);
$location_id = $line->location_id;
// 大类code换去大类id
$category_query = $DB->prepare("
SELECT asset_category_id
FROM mic_asset_category
WHERE asset_category_code = :asset_category_code
");
$category_query->bindvalue(':asset_category_code', $post[0]['asset_category_code']);
if (!$category_query->execute()) {
Flight::error(new Exception(errorInfo($category_query)));
}
$line = $category_query->fetch(PDO::FETCH_OBJ);
$asset_category_id = $line->asset_category_id;
// 根据供应商code 获取供应商ID
$pur_supplier_query = $DB->prepare("
SELECT supplier_id
FROM pur_supplier
WHERE supplier_code = :supplier_code
");
$pur_supplier_query->bindvalue(':supplier_code', $post[0]['supplier_code']);
if (!$pur_supplier_query->execute()) {
Flight::error(new Exception(errorInfo($pur_supplier_query)));
}
$line = $pur_supplier_query->fetch(PDO::FETCH_OBJ);
$supplier_id = $line->supplier_id;
$object = new CreateAsset($DB);
$params =
[
'asset_name' => $post[0]['asset_name'],
'asset_code' => $post[0]['asset_code'],
'asset_nature' => $post[0]['asset_nature'],
'location_id' => $location_id,
'asset_status' => $post[0]['asset_status'],
'asset_model' => $post[0]['asset_model'],
'asset_category_id' => $asset_category_id,
'supplier_id' => $supplier_id,
'asset_description' => $post[0]['asset_description'],
'asset_acquisition_price' => $post[0]['asset_acquisition_price'],
'asset_service_start_date' => $post[0]['asset_service_start_date'],
'asset_ff_1' => $post[0]['asset_ff_1'],
'asset_ff_2' => $post[0]['asset_ff_2'],
'function_id' => $function_id,
'criticality_id' => $criticality_id,
'asset_primary_code' => $post[0]['asset_primary_code'],
];
$result_son = $object->execute($params);
// 找父
$parent_query = $DB->prepare("
SELECT asset_id
FROM asset_list
WHERE asset_primary_code = :asset_primary_code
");
$parent_query->bindvalue(':asset_primary_code', $post[0]['asset_primary_code']);
if (!$parent_query->execute()) {
Flight::error(new Exception(errorInfo($parent_query)));
} elseif ($parent_query->rowcount() == 0) {
Flight::notFound();
}
$parent_query_line = $parent_query->fetch(PDO::FETCH_OBJ);
if (!isset($parent_query_line->asset_id)) {
echo '父编码不存在';
} else {
// 找子
$son_query = $DB->prepare("
SELECT asset_id
FROM asset_list
WHERE asset_code = :asset_code
");
$son_query->bindvalue(':asset_code', $result_son['asset_code']);
if (!$son_query->execute()) {
Flight::error(new Exception(errorInfo($son_query)));
}
$son_query_line = $son_query->fetch(PDO::FETCH_OBJ);
$son_id = $son_query_line->asset_id;
// 进行父子级同步
$treeModel = new NestedSet($DB);
$move_result = $treeModel->addChild($result_son['asset_id'], $parent_query_line->asset_id);
$result = [
'asset_id' => $son_id,
'parent_id' =>$parent_query_line->asset_id,
'details' => '父子级关系'
];
update_log($result);
}
}
CreateAsset use case
<?php
namespace Domain\Asset\Register\UseCase;
use Domain\Asset\Register\Model\Register;
use PDO;
use Exception;
use Domain\UseCase;
use Domain\UseCaseInterface;
use InvalidArgumentException;
use Shared\Json;
use Shared\Exception\AlreadyExistsException;
use Overtrue\Pinyin\Pinyin;
class CreateAsset extends UseCase implements UseCaseInterface
{
private $check;
private $location;
private $query;
private $lpad;
private $code;
public function __construct(PDO $db)
{
$this->db = $db;
$this->location = $this->db->prepare("
SELECT location_id
FROM asset_location
WHERE location_code = :location_code
;");
$this->check = $this->db->prepare("
SELECT asset_code
FROM asset_list
WHERE asset_code = :asset_code
;");
$this->query = $this->db->prepare("
INSERT INTO asset_list (
asset_nature, asset_code, asset_name, asset_name_pinyin, function_id, criticality_id,
supplier_id, manufacturer_id, asset_cost_center_id, location_id,
asset_structure_lft, asset_structure_rgt, asset_primary_code, asset_level,
asset_category_id, asset_class_id, asset_type_id,
asset_serial_number, asset_model, asset_status, asset_creation_time,
asset_creator, asset_alternative_code, asset_ff_1, asset_ff_2, asset_ff_3,
asset_ff_4, asset_ff_5, asset_ff_6, asset_ff_7, asset_ff_8, asset_ff_9, asset_ff_10,
asset_ff_11, asset_ff_12, asset_ff_13, asset_ff_14, asset_ff_15
)
SELECT :asset_nature, :asset_code, :asset_name, :asset_name_pinyin, :function_id, :criticality_id, :supplier_id,
:manufacturer_id, :asset_cost_center_id, :location_id, IFNULL(MAX(asset_structure_rgt), 0) + 1,
IFNULL(MAX(asset_structure_rgt), 0) + 2, :asset_primary_code, 1, :asset_category_id, :asset_class_id, :asset_type_id,
:asset_serial_number, :asset_model, :asset_status, :asset_creation_time,@eamic_user, :asset_alternative_code,
:asset_ff_1, :asset_ff_2, :asset_ff_3, :asset_ff_4, :asset_ff_5, :asset_ff_6, :asset_ff_7,
:asset_ff_8, :asset_ff_9, :asset_ff_10, :asset_ff_11, :asset_ff_12, :asset_ff_13, :asset_ff_14, :asset_ff_15
FROM asset_list
WHERE location_id = :location_id
;");
$this->lpad = $this->db->prepare("
UPDATE asset_list
SET asset_code = LPAD(asset_id, 5, 0)
WHERE asset_id = :asset_id
AND asset_code = ''
;");
$this->code = $this->db->prepare("
SELECT asset_code
FROM asset_list
WHERE asset_id = :asset_id
;");
}
public function execute(array $data)
{
if (!isset($data['asset_name'])) {
throw new InvalidArgumentException('asset_name not provided');
}
if (isset($data['location_code'])) {
$this->location->bindvalue(
':location_code',
$data['location_code']
);
$this->location->execute();
if ($this->location->rowCount() == 0) {
throw new InvalidArgumentException(
$data['location_code'] . ' does not exist'
);
}
$row = $this->location->fetch(PDO::FETCH_OBJ);
$data['location_id'] = $row->location_id;
} elseif (!isset($data['location_id'])) {
throw new InvalidArgumentException(
'location_id/location_code not provided'
);
}
$data['asset_name_pinyin'] = null;
// prettier-ignore
if (class_exists('Overtrue\Pinyin\Pinyin')) {
$pinyin = new Pinyin();
$data['asset_name_pinyin'] = $pinyin->permalink($data['asset_name'], '');
}
$item = new Register($data);
// Check if the code exists already
$this->check->bindvalue(':asset_code', $item->asset_code);
if (!$this->check->execute()) {
throw new Exception(Json::errorInfo($this->check));
} elseif ($this->check->rowCount() > 0 && $item->asset_code != '') {
throw new AlreadyExistsException();
}
// prettier-ignore
{
$this->query->bindvalue(':location_id', $data['location_id'], PDO::PARAM_INT);
$this->query->bindvalue(':asset_primary_code', $item->asset_code);
$this->query->bindvalue(':asset_creation_time', Date('Y-m-d H:i:s'));
$this->query->bindvalue(':asset_nature', $item->asset_nature, PDO::PARAM_INT);
$this->query->bindvalue(':asset_code', $item->asset_code);
$this->query->bindvalue(':asset_name', $item->asset_name);
$this->query->bindvalue(':asset_name_pinyin', $data['asset_name_pinyin']);
$this->query->bindvalue(':asset_model', $item->asset_model);
$this->query->bindvalue(':function_id', $item->function_id, PDO::PARAM_INT);
$this->query->bindvalue(':criticality_id', $item->criticality_id, PDO::PARAM_INT);
$this->query->bindvalue(':supplier_id', $item->supplier_id, PDO::PARAM_INT);
$this->query->bindvalue(':manufacturer_id', $item->manufacturer_id, PDO::PARAM_INT);
$this->query->bindvalue(':asset_cost_center_id', $item->asset_cost_center_id, PDO::PARAM_INT);
$this->query->bindvalue(':asset_serial_number', $item->asset_serial_number);
$this->query->bindvalue(':asset_category_id', $item->asset_category_id, PDO::PARAM_INT);
$this->query->bindvalue(':asset_class_id', $item->asset_class_id, PDO::PARAM_INT);
$this->query->bindvalue(':asset_type_id', $item->asset_type_id, PDO::PARAM_INT);
$this->query->bindvalue(':asset_status', $item->asset_status, PDO::PARAM_INT);
$this->query->bindvalue(':asset_alternative_code', $item->asset_alternative_code);
$this->query->bindvalue(':asset_ff_1', $item->asset_ff_1);
$this->query->bindvalue(':asset_ff_2', $item->asset_ff_2);
$this->query->bindvalue(':asset_ff_3', $item->asset_ff_3);
$this->query->bindvalue(':asset_ff_4', $item->asset_ff_4);
$this->query->bindvalue(':asset_ff_5', $item->asset_ff_5);
$this->query->bindvalue(':asset_ff_6', $item->asset_ff_6);
$this->query->bindvalue(':asset_ff_7', $item->asset_ff_7);
$this->query->bindvalue(':asset_ff_8', $item->asset_ff_8);
$this->query->bindvalue(':asset_ff_9', $item->asset_ff_9);
$this->query->bindvalue(':asset_ff_10', $item->asset_ff_10);
$this->query->bindvalue(':asset_ff_11', $item->asset_ff_11);
$this->query->bindvalue(':asset_ff_12', $item->asset_ff_12);
$this->query->bindvalue(':asset_ff_13', $item->asset_ff_13);
$this->query->bindvalue(':asset_ff_14', $item->asset_ff_14);
$this->query->bindvalue(':asset_ff_15', $item->asset_ff_15);
}
if (!$this->query->execute()) {
throw new Exception(Json::errorInfo($this->query));
}
$new_id = $this->db->lastInsertId();
$this->lpad->bindvalue(':asset_id', $new_id, PDO::PARAM_INT);
if (!$this->lpad->execute()) {
throw new Exception(Json::errorInfo($this->lpad));
}
// Get the asset code (it may have been changed by a trigger)
$this->code->bindvalue(':asset_id', $new_id, PDO::PARAM_INT);
if (!$this->code->execute()) {
throw new Exception(Json::errorInfo($this->code));
} elseif ($this->code->rowCount() == 0) {
throw new Exception('The asset has not been created');
}
$item = $this->code->fetch(PDO::FETCH_OBJ);
$asset_code = $item->asset_code;
return [
'asset_id' => $new_id,
'asset_code' => $asset_code,
];
}
}
NestedSet
<?php /** * PDONestedSet - Using Nested Sets Pattern to Model Tree Structure in PHP * PHP Version 5.0.0 * Version 5.6.4 * @package PDONestedSet * @link https://github.com/ben-nsng/php-pdo-nested-set * @author Ngai Sing Ng (Ben) <[email protected]> * @copyright 2015 Ngai Sing Ng * @license The MIT License (MIT) * @note This program is distributed in the hope that it will be useful - WITHOUT * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or * FITNESS FOR A PARTICULAR PURPOSE. */ namespace { Class NestedSet { /** * Database object * @type resource */ private $database; /** * Table name * @type string */ private $table = 'asset_list'; /** * Constructor, defined PDO object * @param object $dbh */ public function __construct(PDO $dbh) { $this->database = new PDONestedSet\database($dbh); } /** * Change the current table to manage tree structure * @param object $table table name */ public function changeTable($table) { $this->table = $table; } /** * Add root to the table (for initialization only) */ public function addRoot() { $sql = 'SELECT COUNT(1) AS row_count FROM asset_list WHERE asset_level=0;'; $query = $this->database->execute($sql); $result = $query->result(); if($result[0]->row_count != '0') { return false; // root exists, exit } $sql = 'INSERT INTO asset_list(location_name, asset_structure_lft, asset_structure_rgt, asset_level) VALUES(?, ?, ?, ?)'; $query = $this->database->execute($sql, array('root', '1', '2', '0')); } /** * Add new node to the tree structure * @param string $location_name node name * @param int $node_parent_id parent node asset_id * @return int new node asset_id */ public function addNode($location_name = '', $node_parent_id = '') { //if no parent define, add to root node if($node_parent_id == '') { $sql = 'SELECT asset_id FROM asset_list WHERE asset_level=0'; $query = $this->database->execute($sql); // check if root node exists if($query->numRows() == 0) { $this->addRoot(); return $this->addNode($location_name, $node_parent_id); } $result = $query->result(); $node_parent_id = $result[0]->asset_id; } //check if node_parent_id exists $sql = 'SELECT asset_id, asset_structure_lft, asset_structure_rgt, asset_level FROM asset_list WHERE asset_id = ?'; $query = $this->database->execute($sql, array($node_parent_id)); if($query->numRows() == 0) { return false; // no parent ? } $result = $query->result(); $parent_lft = $result[0]->asset_structure_lft; $parent_rht = $result[0]->asset_structure_rgt; $parent_lvl = $result[0]->asset_level; $this->database->transStart(); //shift the node to give some room for new node $sql = 'UPDATE asset_list SET asset_structure_lft = CASE WHEN asset_structure_lft > ? THEN asset_structure_lft + 2 ELSE asset_structure_lft END, asset_structure_rgt = CASE WHEN asset_structure_rgt >= ? THEN asset_structure_rgt + 2 ELSE asset_structure_rgt END WHERE (asset_nature = 0 OR asset_nature = 1) AND asset_structure_rgt >= ?'; $this->database->execute($sql, array($parent_rht, $parent_rht, $parent_rht)); $sql = 'INSERT INTO asset_list(location_name, asset_structure_lft, asset_structure_rgt, asset_level, asset_parent_id) VALUES(?, ?, ?, ?, ?)'; $this->database->execute($sql, array($location_name, $parent_rht, $parent_rht + 1, $parent_lvl + 1, $node_parent_id)); $this->database->transEnd(); return $this->database->lastInsertId(); } /** * Select all nodes from the table */ public function selectAll() { $sql = 'SELECT asset_id, location_name, asset_level, asset_parent_id, FORMAT((((asset_structure_rgt - asset_structure_lft) -1) / 2),0) AS cnt_children, CASE WHEN asset_structure_rgt - asset_structure_lft > 1 THEN 1 ELSE 0 END AS is_branch FROM asset_list ORDER BY asset_structure_lft'; return $this->database->execute($sql); } /** * Move existing node into node 2 * @param int $node_id_1 asset_id of node 1 SOURCE * @param int $node_id_2 asset_id of node 2 TARGET */ public function addChild($node_id_1, $node_id_2) { if($node_id_1 == $node_id_2) { return false; //same node } // check if node asset_id 1, 2 exist $sql = 'SELECT asset_id, asset_structure_lft, asset_structure_rgt, asset_level, @myLocation := location_id, asset_primary_code FROM asset_list WHERE asset_id = ? OR asset_id = ?'; $query = $this->database->execute($sql, array($node_id_1, $node_id_2)); if($query->numRows() != 2) { return false; //no node } // save the result $result = $query->result(); if($result[0]->asset_id == $node_id_1) { $node1 = $result[0]; $node2 = $result[1]; } else { $node1 = $result[1]; $node2 = $result[0]; } $node1_size = $node1->asset_structure_rgt - $node1->asset_structure_lft + 1; $this->database->transStart(); // temporary "remove" moving node $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft, asset_structure_rgt = 0 - asset_structure_rgt, asset_level = asset_level + (?), asset_primary_code = ? WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node2->asset_level - $node1->asset_level + 1, $node2->asset_primary_code, $node1->asset_structure_lft, $node1->asset_structure_rgt)); // decrease left / right position for current node $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft - (?) WHERE asset_structure_lft >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1->asset_structure_lft)); $sql = 'UPDATE asset_list SET asset_structure_rgt = asset_structure_rgt - (?) WHERE asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1->asset_structure_rgt)); // increase left / right position for future node $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft + (?) WHERE asset_structure_lft >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node2->asset_structure_rgt > $node1->asset_structure_rgt ? $node2->asset_structure_rgt - $node1_size : $node2->asset_structure_rgt)); $sql = 'UPDATE asset_list SET asset_structure_rgt = asset_structure_rgt + (?) WHERE asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node2->asset_structure_rgt > $node1->asset_structure_rgt ? $node2->asset_structure_rgt - $node1_size : $node2->asset_structure_rgt)); // move the node to new position $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft + (?), asset_structure_rgt = 0 - asset_structure_rgt + (?) WHERE asset_structure_lft <= ? AND asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array( $node2->asset_structure_rgt > $node1->asset_structure_rgt ? $node2->asset_structure_rgt - $node1->asset_structure_rgt - 1 : $node2->asset_structure_rgt - $node1->asset_structure_rgt - 1 + $node1_size, $node2->asset_structure_rgt > $node1->asset_structure_rgt ? $node2->asset_structure_rgt - $node1->asset_structure_rgt - 1 : $node2->asset_structure_rgt - $node1->asset_structure_rgt - 1 + $node1_size, 0 - $node1->asset_structure_lft, 0 - $node1->asset_structure_rgt)); // update parent $sql = 'UPDATE asset_list SET asset_parent_id = ? WHERE asset_id = ? AND location_id = @myLocation'; $this->database->execute($sql, array($node2->asset_id, $node1->asset_id)); $this->database->transEnd(); } /** * Move existing node before node 2 * @param int $node_id_1 asset_id of node 1 SOURCE * @param int $node_id_2 asset_id of node 2 TARGET */ public function addBefore($node_id_1, $node_id_2) { if($node_id_1 == $node_id_2) { return false; //same node } // check if node asset_id 1, 2 exist $sql = 'SELECT asset_id, asset_structure_lft, asset_structure_rgt, asset_level, asset_parent_id, @myLocation := location_id, asset_code, asset_primary_code FROM asset_list WHERE asset_id = ? OR asset_id = ?'; $query = $this->database->execute($sql, array($node_id_1, $node_id_2)); if($query->numRows() != 2) { return false; //no node } // save the result $result = $query->result(); if($result[0]->asset_id == $node_id_1) { $node1 = $result[0]; $node2 = $result[1]; } else { $node1 = $result[1]; $node2 = $result[0]; } $this->database->transStart(); if ($node2->asset_level == 1 || $node2->asset_parent_id == 0) { // same level, put node 1 before node 2 $node1_size = $node1->asset_structure_rgt - $node1->asset_structure_lft + 1; $node2_size = $node2->asset_structure_rgt - $node1->asset_structure_lft + 1; $sql = 'SELECT @right_max := MAX(asset_structure_rgt) FROM asset_list WHERE location_id = @myLocation AND asset_nature IN (1 , 0); UPDATE asset_list SET asset_structure_lft = @right_max + asset_structure_lft - (? - 1), asset_structure_rgt = @right_max + asset_structure_rgt - (? - 1), asset_level = asset_level - (? - 1), asset_primary_code = ? WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft, $node1->asset_structure_lft, $node1->asset_level, $node1->asset_code, $node1->asset_structure_lft, $node1->asset_structure_rgt)); // shift other node to correct position $sql = 'UPDATE asset_list SET asset_structure_lft = CASE WHEN asset_structure_lft > ? THEN asset_structure_lft - ? ELSE asset_structure_lft END, asset_structure_rgt = CASE WHEN asset_structure_rgt >= ? THEN asset_structure_rgt - ? ELSE asset_structure_rgt END WHERE asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft, $node1_size, $node1->asset_structure_rgt, $node1_size, $node1->asset_structure_rgt)); // update parent $sql = 'UPDATE asset_list SET asset_parent_id = 0 WHERE asset_id = ? AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_id)); } else { // if not in same level, put it in same levels if($node1->asset_level != $node2->asset_level || $node1->asset_parent_id != $node2->asset_parent_id) { $this->addChild($node_id_1, $node2->asset_parent_id); return $this->addBefore($node_id_1, $node_id_2); } // same level, put node 1 before node 2 $node1_size = $node1->asset_structure_rgt - $node1->asset_structure_lft + 1; $node2_size = $node2->asset_structure_rgt - $node1->asset_structure_lft + 1; // temporary "remove" moving node $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft, asset_structure_rgt = 0 - asset_structure_rgt WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft, $node1->asset_structure_rgt)); if($node1->asset_structure_lft > $node2->asset_structure_lft) { //move left //shift the node to right to give some room $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft + ?, asset_structure_rgt = asset_structure_rgt + ? WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1_size, $node2->asset_structure_lft, $node1->asset_structure_lft)); //move back the node1 $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft - ?, asset_structure_rgt = 0 - asset_structure_rgt - ? WHERE asset_structure_lft <= ? AND asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft - $node2->asset_structure_lft, $node1->asset_structure_lft - $node2->asset_structure_lft, 0 - $node1->asset_structure_lft, 0 - $node1->asset_structure_rgt)); } else { //shift the node to left to give some room $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft - ?, asset_structure_rgt = asset_structure_rgt - ? WHERE asset_structure_lft >= ? AND asset_structure_rgt < ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1_size, $node1->asset_structure_rgt, $node2->asset_structure_lft)); //move back the node1 $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft + ?, asset_structure_rgt = 0 - asset_structure_rgt + ? WHERE asset_structure_lft <= ? AND asset_structure_rgt >= ?, AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node2->asset_structure_lft - $node1->asset_structure_rgt - 1, $node2->asset_structure_lft - $node1->asset_structure_rgt - 1, 0 - $node1->asset_structure_lft, 0 - $node1->asset_structure_rgt)); } } $this->database->transEnd(); } /** * Move existing node after node 2 * @param int $node_id_1 asset_id of node 1 * @param int $node_id_2 asset_id of node 2 */ public function addAfter($node_id_1, $node_id_2) { if($node_id_1 == $node_id_2) { return false; //same node } // check if node asset_id 1, 2 exist $sql = 'SELECT asset_id, asset_structure_lft, asset_structure_rgt, asset_level, asset_parent_id, @myLocation := location_id FROM asset_list WHERE asset_id=? OR asset_id=?'; $query = $this->database->execute($sql, array($node_id_1, $node_id_2)); if($query->numRows() != 2) { return false; //no node } // save the result $result = $query->result(); if($result[0]->asset_id == $node_id_1) { $node1 = $result[0]; $node2 = $result[1]; } else { $node1 = $result[1]; $node2 = $result[0]; } $this->database->transStart(); // if not in same level, put it in same level if($node1->asset_level != $node2->asset_level || $node1->asset_parent_id != $node2->asset_parent_id) { $this->addChild($node_id_1, $node2->asset_parent_id); return $this->addAfter($node_id_1, $node_id_2); } // same level, put node 1 before node 2 $node1_size = $node1->asset_structure_rgt - $node1->asset_structure_lft + 1; $node2_size = $node2->asset_structure_rgt - $node1->asset_structure_lft + 1; // temporary "remove" moving node $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft, asset_structure_rgt = 0 - asset_structure_rgt WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft, $node1->asset_structure_rgt)); if($node1->asset_structure_lft > $node2->asset_structure_lft) { //move left //shift the node to right to give some room $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft + ?, asset_structure_rgt = asset_structure_rgt + ? WHERE asset_structure_lft > ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1_size, $node2->asset_structure_rgt, $node1->asset_structure_lft)); //move back the node1 $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft - ?, asset_structure_rgt = 0 - asset_structure_rgt - ? WHERE asset_structure_lft <= ? AND asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1->asset_structure_lft - $node2->asset_structure_rgt - 1, $node1->asset_structure_lft - $node2->asset_structure_rgt - 1, 0 - $node1->asset_structure_lft, 0 - $node1->asset_structure_rgt)); } else { //shift the node to left to give some room $sql = 'UPDATE asset_list SET asset_structure_lft = asset_structure_lft - ?, asset_structure_rgt = asset_structure_rgt - ? WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node1_size, $node1_size, $node1->asset_structure_rgt, $node2->asset_structure_rgt)); //move back the node1 $sql = 'UPDATE asset_list SET asset_structure_lft = 0 - asset_structure_lft + ?, asset_structure_rgt = 0 - asset_structure_rgt + ? WHERE asset_structure_lft <= ? AND asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($node2->asset_structure_rgt - $node1->asset_structure_rgt, $node2->asset_structure_rgt - $node1->asset_structure_rgt, 0 - $node1->asset_structure_lft, 0 - $node1->asset_structure_rgt)); } $this->database->transEnd(); } /** * Delete existing node * @param int $node_id asset_id of node */ public function deleteNode($node_id) { $sql = 'SELECT asset_id, asset_structure_lft, asset_structure_rgt, asset_level, @myLocation := location_id FROM asset_list WHERE asset_id=?'; $query = $this->database->execute($sql, $node_id); if($query->numRows() == 0) { return false; //no node } $result = $query->result(); $asset_structure_lft = $result[0]->asset_structure_lft; $asset_structure_rgt = $result[0]->asset_structure_rgt; $asset_level = $result[0]->asset_level; $this->database->transStart(); // remove parent first $sql = 'UPDATE asset_list SET asset_parent_id = NULL WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ? AND location_id = @myLocation'; $this->database->execute($sql, array($asset_structure_lft, $asset_structure_rgt)); // delete nodes /* $sql = 'DELETE * FROM asset_list WHERE asset_structure_lft >= ? AND asset_structure_rgt <= ?'; $this->database->execute($sql, array($asset_structure_lft, $asset_structure_rgt)); */ $sql = 'DELETE FROM asset_list WHERE asset_parent_id IS NULL AND asset_level <> 0 AND location_id = @myLocation'; $this->database->execute($sql); $node_tmp = $asset_structure_rgt - $asset_structure_lft + 1; // shift other node to correct position $sql = 'UPDATE asset_list SET asset_structure_lft = CASE WHEN asset_structure_lft > ? THEN asset_structure_lft - ? ELSE asset_structure_lft END, asset_structure_rgt = CASE WHEN asset_structure_rgt >= ? THEN asset_structure_rgt - ? ELSE asset_structure_rgt END WHERE asset_structure_rgt >= ? AND (asset_nature = 0 OR asset_nature = 1) AND location_id = @myLocation'; $this->database->execute($sql, array($asset_structure_lft, $node_tmp, $asset_structure_rgt, $node_tmp, $asset_structure_rgt)); $this->database->transEnd(); } } } namespace PDONestedSet { class database { private $dh; private $trans; public function __construct(\PDO $dbh) { $this->dh = $dbh; $this->trans = false; } public function lastInsertId() { return $this->dh->lastInsertId(); } public function transStart() { if($this->trans) return; $this->trans = true; $this->dh->beginTransaction(); } public function transEnd() { $this->trans = false; $this->dh->commit(); } public function execute($sql, $placeholders = array()) { $stmt = new statement($this->dh); return $stmt->query($sql, $placeholders); } } class statement { private $dh; private $stmt; private $result; private $result_array; public function __construct($dh) { $this->dh = $dh; } public function query($sql, $placeholders = array()) { if(count($placeholders) == 0) $this->stmt = $this->dh->query($sql); else { if(!is_array($placeholders)) $placeholders = array($placeholders); $this->stmt = $this->dh->prepare($sql); $this->stmt->execute($placeholders); } return $this; } public function result($array = false) { if(!$array && $this->result != null) return $this->result; if($array && $this->result_array != null) return $this->result_array; if($this->stmt != null) { if(!$array) { $this->result = $this->stmt->fetchAll(\PDO::FETCH_OBJ); return $this->result; } else { $this->result_array = $this->stmt->fetchAll(\PDO::FETCH_ASSOC); return $this->result_array; } } return array(); } public function numRows() { if($this->stmt != null) return $this->stmt->rowCount(); return 0; } } }
标签：code,rgt,父子,asset,query,lz,id,structure From： https://www.cnblogs.com/xiaoyantongxue/p/16791020.html