QueryGeneratorService.php

<?php

namespace UnicaenTbl\Service;

use UnicaenTbl\Entity\TableauBord;
use UnicaenTbl\Options\Traits\ModuleOptionsAwareTrait;
use UnicaenTbl\Service\Traits\SchemaServiceAwareTrait;

/**
 *
 *
 * @author Laurent LÉCLUSE <laurent.lecluse at unicaen.fr>
 */
class QueryGeneratorService extends AbstractService
{
    use SchemaServiceAwareTrait;
    use ModuleOptionsAwareTrait;

    const AG_BEGIN = '-- AUTOMATIC GENERATION --';
    const AG_END   = '-- END OF AUTOMATIC GENERATION --';

    /**
     * @var TableauBord[]
     */
    protected $tableauxBords = [];



    /**
     * Mettre à jour toutes les infos dans la BDD
     *
     * @return self
     */
    public function updateProcedures()
    {
        $this->tableauxBords = $this->getServiceSchema()->getTableauBords();

        foreach ($this->tableauxBords as $i => $tbl) {
            if ($tbl->getCustomCalculProc()) {
                unset($this->tableauxBords[$i]);
            }
        }

        $decl  = $this->makePackageDeclaration();
        $body  = $this->makePackageBody();
        $procs = $this->makeProcBodies();

        $this->getEntityManager()->getConnection()->exec($decl);
        $this->getEntityManager()->getConnection()->exec($body);

        return $this;
    }



    /**
     * Retourne le code source du package de gestion des tableaux de bord
     *
     * @return string
     */
    protected function getPackageDeclaration()
    {
        $sql    = "SELECT TEXT FROM USER_SOURCE WHERE NAME = '" . $this->getOptionsModule()->getPackage() . "' AND type = 'PACKAGE'";
        $result = $this->query($sql, [], 'TEXT');

        return implode("", $result);
    }



    /**
     * Retourne le code source du package de gestion des tableaux de bord
     *
     * @return string
     */
    protected function getPackageBody()
    {
        $sql    = "SELECT TEXT FROM USER_SOURCE WHERE NAME = '" . $this->getOptionsModule()->getPackage() . "' AND type = 'PACKAGE BODY'";
        $result = $this->query($sql, [], 'TEXT');

        return implode("", $result);
    }



    /**
     * @param string $viewName
     *
     * @return string
     */
    protected function getViewDefinition($viewName)
    {
        $sql    = "SELECT TEXT FROM USER_VIEWS WHERE VIEW_NAME = '" . $viewName . "'";
        $result = $this->query($sql, [], 'TEXT');

        $definition = $result[0];

        $definition = trim($definition);

        return $definition;
    }



    /**
     * Construit toutes les déclarations de procédures
     *
     * @return array
     */
    protected function makeProcDeclarations()
    {
        $result = [];
        foreach ($this->tableauxBords as $tbl) {
            $result[$tbl->getName()] = $this->makeProcDeclaration($tbl);
        }

        return $result;
    }



    /**
     * Construit tous les corps de procédures
     *
     * @return array
     */
    protected function makeProcBodies()
    {
        $result = [];
        foreach ($this->tableauxBords as $tbl) {
            $result[$tbl->getName()] = $this->makeProcBody($tbl);
        }

        return $result;
    }



    /**
     * Constuit la nouvelle déclaration du package TBL
     *
     * @return string
     */
    protected function makePackageDeclaration()
    {
        $src  = $this->getPackageDeclaration();
        $decl = implode("\n", $this->makeProcDeclarations());

        return $this->updatePackageContent($src, $decl);
    }



    /**
     * Constuit la nouvelle déclaration du package TBL
     *
     * @return string
     */
    protected function makePackageBody()
    {
        $src  = $this->getPackageBody();
        $decl = implode("\n\n\n\n", $this->makeProcBodies());

        return $this->updatePackageContent($src, $decl);
    }



    /**
     * Mise à jour du contenu d'un package (déclaration ou corps)
     *
     * @param string $packageSource
     * @param string $newContent
     *
     * @return string
     */
    protected function updatePackageContent($packageSource, $newContent)
    {
        $src = $packageSource;
        if (null === $begin = strpos($packageSource, self::AG_BEGIN)) {
            throw new Exception('Le tag indiquant le début de la zone automatique du package n\'a pas été trouvée');
        }

        if (null === $end = strpos($packageSource, self::AG_END)) {
            throw new Exception('Le tag indiquant la fin de la zone automatique du package n\'a pas été trouvée');
        }

        $src = 'CREATE OR REPLACE '
            . substr($packageSource, 0, $begin + strlen(self::AG_BEGIN))
            . "\n\n" . $newContent . "\n\n  "
            . substr($packageSource, $end);

        return $src;
    }



    /**
     * Génère une déclaration de procédure pour une table donnée
     *
     * @param TableauBord $tbl
     *
     * @return string
     */
    protected function makeProcDeclaration(TableauBord $tbl)
    {
        return "  PROCEDURE C_" . strtoupper($tbl->getName()) . "(useParams BOOLEAN DEFAULT FALSE);";
    }



    /**
     * Génère un corps de procédure pour une table donnée
     *
     * @param TableauBord $tbl
     *
     * @return string
     */
    protected function makeProcBody(TableauBord $tbl)
    {
        $tableauBord = strtoupper($tbl->getName());
        $table       = strtoupper($tbl->getTableName(true));
        $view        = strtoupper($tbl->getViewName(true));
        $sequence    = strtoupper($tbl->getSequenceName(true));

        if (empty($tbl->getKeyColumns())) {
            throw new \Exception('Le tableau de bord ' . $tbl->getName() . ' n\'a pas de contrainte d\'unicité.');
        }

        $maxKeyColLen = 0;
        foreach ($tbl->getKeyColumns() as $column) {
            if (strlen($column) > $maxKeyColLen) {
                $maxKeyColLen = strlen($column);
            }
        }

        $maxColLen = 0;
        foreach ($tbl->getColumns() as $column) {
            if (strlen($column) > $maxColLen) {
                $maxColLen = strlen($column);
            }
        }

        $join     = [];
        $testNull = [];
        foreach ($tbl->getKeyColumns() as $column) {
            $colLen   = strlen($column);
            $colAjust = str_pad('', $maxColLen - $colLen);

            if ($column->isNullable()) {
                $join[] = "COALESCE(t.$column,0)$colAjust = COALESCE(v.$column,0)\n";
            } else {
                $join[] = "t.$column$colAjust             = v.$column\n";
            }
            $testNull[] = "d.$column IS NULL\n";
        }
        $join     = trim(implode("        AND ", $join));
        $testNull = trim(implode("        AND ", $testNull));

        $testDiff = [];
        $cols     = [];
        foreach ($tbl->getColumns() as $column) {
            $colLen   = strlen($column);
            $colAjust = str_pad('', $maxColLen - $colLen);

            if ($column->isInView()) {
                if ($column->isNullable()) {
                    $testDiff[] = "COALESCE(t.$column,0)$colAjust = COALESCE(v.$column,0)\n";
                } else {
                    $testDiff[] = "t.$column$colAjust             = v.$column\n";
                }
            }
            if ($column == 'ID') {
                $cols[] = 'ID';
            } else {
                $cols[] = "v.$column";
            }
        }
        $testDiff = trim(implode("        AND ", $testDiff));
        foreach ($cols as $c => $col) {
            if ('ID' == $col) {
                $cols[$c] = "CASE WHEN 
            $testDiff
      THEN -1 ELSE t.ID END ID";
            }
        }
        $cols = trim(implode(",\n      ", $cols));

        $view = $this->getViewDefinition($view);
        $view = str_replace('\'', '\'\'', $view);
        $view = str_replace("\n", "\n        ", $view);

        $sql = "  PROCEDURE C_$tableauBord(useParams BOOLEAN DEFAULT FALSE) IS
  TYPE r_cursor IS REF CURSOR;
  c r_cursor;
  d $table%rowtype;
  viewQuery CLOB;
  BEGIN
    viewQuery := '$view';
    
    OPEN c FOR '
    SELECT
      $cols
    FROM
      (' || QUERY_APPLY_PARAMS(viewQuery, useParams) || ') v
      FULL JOIN $table t ON 
            $join
    WHERE ' || PARAMS_MAKE_FILTER(useParams);
    LOOP
      FETCH c INTO d; EXIT WHEN c%NOTFOUND;

      IF d.id IS NULL THEN
        d.id := $sequence.NEXTVAL;
        INSERT INTO $table values d;
      ELSIF 
            $testNull
      THEN
        DELETE FROM $table WHERE id = d.id;
      ELSIF d.id <> -1 THEN
        UPDATE $table SET row = d WHERE id = d.id;
      END IF;
    END LOOP;
    CLOSE c;
  END;
";

        return $sql;
    }
}