partkeepr

fork of partkeepr
git clone https://git.e1e0.net/partkeepr.git
Log | Files | Refs | Submodules | README | LICENSE

Version20150708120022.php (4198B)


      1 <?php
      2 
      3 namespace PartKeepr\CoreBundle\DoctrineMigrations;
      4 
      5 use Doctrine\DBAL\Schema\Schema;
      6 
      7 /**
      8  * Fixes the category trees due to the migration of doctrine2-nestedset to DoctrineExtensions.
      9  */
     10 class Version20150708120022 extends BaseMigration
     11 {
     12     /**
     13      * @param Schema $schema
     14      */
     15     public function up(Schema $schema)
     16     {
     17         $this->performDatabaseUpgrade();
     18 
     19         $this->fixTree('PartCategory');
     20         $this->fixTree('FootprintCategory');
     21         $this->fixTree('StorageLocationCategory');
     22 
     23         $this->createRootNode('partkeepr.part.category_service');
     24         $this->createRootNode('partkeepr.storage_location.category_service');
     25         $this->createRootNode('partkeepr.footprint.category_service');
     26     }
     27 
     28     protected function createRootNode($service)
     29     {
     30         $this->getContainer()->get($service)->ensureRootNodeExists();
     31     }
     32 
     33     /**
     34      * Fixes the tree for a given table due to the migration of doctrine2-nestedset to DoctrineExtensions.
     35      *
     36      * @param string $table The table name to fix
     37      *
     38      * @throws \Doctrine\DBAL\DBALException
     39      */
     40     public function fixTree($table)
     41     {
     42         $nodes = $this->getNodeIds($table);
     43 
     44         $queryBuilder = $this->connection->createQueryBuilder();
     45         $queryBuilder->update($table)
     46             ->set('parent_id', ':parent')
     47             ->set('root', ':root')
     48             ->set('lvl', ':level')
     49             ->where('id = :id');
     50 
     51         foreach ($nodes as $node) {
     52             $parent = $this->fetchParent($table, $node['id']);
     53             $level = $this->getLevel($table, $node['id']);
     54 
     55             if ($parent !== false) {
     56                 $this->connection->executeUpdate(
     57                     $queryBuilder->getSQL(),
     58                     [
     59                         ':parent' => $parent,
     60                         ':id'     => $node['id'],
     61                         ':level'  => $level,
     62                         ':root'   => 1,
     63                     ]
     64                 );
     65             } else {
     66                 $this->connection->executeUpdate(
     67                     $queryBuilder->getSQL(),
     68                     [
     69                         ':parent' => null,
     70                         ':id'     => $node['id'],
     71                         ':root'   => 1,
     72                         ':level'  => 0,
     73                     ]
     74                 );
     75             }
     76         }
     77     }
     78 
     79     /**
     80      * Fetches the parent node for a table and ID.
     81      *
     82      * @param $table
     83      * @param $id
     84      *
     85      * @return mixed
     86      */
     87     public function fetchParent($table, $id)
     88     {
     89         $queryBuilder = $this->connection->createQueryBuilder();
     90 
     91         $queryBuilder->select('parent.id')
     92             ->from($table, 'node')
     93             ->from($table, 'parent')
     94             ->where('parent.lft < node.lft')
     95             ->andWhere('parent.rgt > node.rgt')
     96             ->andWhere('node.id = :nodeid')
     97             ->orderBy('parent.rgt - parent.lft')
     98             ->setMaxResults(1);
     99 
    100         return $this->connection->fetchColumn($queryBuilder->getSQL(), [':nodeid' => $id], 0);
    101     }
    102 
    103     /**
    104      * Returns the node IDs for the table.
    105      *
    106      * @param $table
    107      *
    108      * @return array
    109      */
    110     public function getNodeIds($table)
    111     {
    112         $qb = $this->connection->createQueryBuilder();
    113 
    114         $qb->select('id')
    115             ->from($table)
    116             ->orderBy('id', 'ASC');
    117 
    118         return $this->connection->fetchAll($qb->getSQL());
    119     }
    120 
    121     /**
    122      * Returns the level for a given table and ID.
    123      *
    124      * @param $table
    125      * @param $id
    126      *
    127      * @return mixed
    128      */
    129     public function getLevel($table, $id)
    130     {
    131         $qb = $this->connection->createQueryBuilder();
    132 
    133         $qb->select('COUNT(*) AS level')
    134             ->from($table, 'node')
    135             ->from($table, 'parent')
    136             ->where('node.lft > parent.lft')
    137             ->andWhere('node.lft < parent.rgt')
    138             ->andWhere('node.id = :nodeid');
    139 
    140         return $this->connection->fetchAssoc($qb->getSQL(), [':nodeid' => $id])['level'];
    141     }
    142 
    143     /**
    144      * @param Schema $schema
    145      */
    146     public function down(Schema $schema)
    147     {
    148         // this down() migration is auto-generated, please modify it to your needs
    149     }
    150 }