lz 父子级
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) <ben.nsng@gmail.com> * @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; } } }
