DbDiffProcess.php

<?php

namespace UnicaenTbl\Process;

use UnicaenTbl\Service\BddServiceAwareTrait;
use UnicaenTbl\TableauBord;

/**
 * Description of DbDiffProcess
 *
 * @author LECLUSE Laurent <laurent.lecluse at unicaen.fr>
 */
class DbDiffProcess implements ProcessInterface
{
    use BddServiceAwareTrait;

    protected TableauBord $tableauBord;

    /**
     * Paramètres de filtres en entrée
     */
    protected array $params;

    /**
     * Nom de la vue source
     */
    protected string $view;

    /**
     * Nom de la table de destination
     */
    protected string $table;

    /**
     * Liste des colonnes à transférer
     */
    protected array $cols;

    /**
     * Clé pour identifier une ligne (contrainte d'unicité obligatoire)
     */
    protected array $key;

    /**
     * Liste des valeurs par défaut pour remplacer les NULL au cas où
     *
     * @var array
     */
    protected array $keyValuesIfNull = [];



    public function run(TableauBord $tableauBord, array $params = []): void
    {
        $this->tableauBord = $tableauBord;
        $this->params = $params;

        $defaultView = 'V_TBL_' . strtoupper($tableauBord->getName());
        $defaultTable = 'TBL_' . strtoupper($tableauBord->getName());

        $this->view = $tableauBord->getOption('view', $defaultView, 'Une vue doit être fournie comme source de données');
        $this->table = $tableauBord->getOption('table', $defaultTable, 'Une table doit être fournie comme destination de données');

        $this->cols = $tableauBord->getOption('cols', [], 'Les colonnes doivent être listées');
        $this->key = $tableauBord->getOption('key', [], 'La clé d\'unicité doit être définie');
        $this->keyValuesIfNull = $tableauBord->getOption('key_values_if_null', []);

        $sql = $this->diffQuery($params);
//sqlDump($sql);return;
        $conn = $this->getServiceBdd()->getEntityManager()->getConnection();
        $conn->beginTransaction();

        $stmt = $conn->executeQuery($sql);
        try{
            while ($data = $stmt->fetchAssociative()) {
                $srcId = (int)$data['UNICAEN_TBL_SRC_ID'];
                $destId = (int)$data['UNICAEN_TBL_DEST_ID'];
                unset($data['UNICAEN_TBL_SRC_ID']);
                unset($data['UNICAEN_TBL_DEST_ID']);

                if ($srcId != 0 && $destId != 0) {
                    $conn->update($this->table, $data, ['ID' => $destId]);
                }
                if ($srcId == 0) {
                    $conn->delete($this->table, ['ID' => $destId]);
                }
                if ($destId == 0) {
                    $conn->insert($this->table, $data);
                }
            }

            $conn->commit();
        } catch (\Exception $e) {
            $conn->rollBack();
            throw $e;
        }
    }



    protected function sourceQuery(): string
    {
        $query = $this->getServiceBdd()->getViewDefinition($this->view);

        return $query;
    }



    protected function diffQuery(array $params): string
    {
        $bdd = $this->getServiceBdd();

        $viewSql = $this->sourceQuery();
        $viewSql = $bdd->injectKey($viewSql, $params);

        $cols = implode(',', $this->cols);
        $where = $bdd->makeWhere($params);

        $joinTest = '';
        foreach ($this->key as $keycol) {
            if ($joinTest != '') $joinTest .= ' AND ';

            if (array_key_exists($keycol, $this->keyValuesIfNull)){
                $valueIfNull = $bdd->escape($this->keyValuesIfNull[$keycol]);
                $joinTest .= "COALESCE(src.$keycol,$valueIfNull) = COALESCE(dest.$keycol,$valueIfNull)";
            }else{
                $joinTest .= "src.$keycol = dest.$keycol";
            }
        }


        $sql = "
        SELECT
          dest.ID UNICAEN_TBL_DEST_ID,
          src.*
        FROM
            (SELECT ID, $cols FROM $this->table $where) dest
            FULL JOIN (SELECT rownum UNICAEN_TBL_SRC_ID, $cols FROM ($viewSql) $where) src ON $joinTest
        WHERE
            dest.id IS NULL OR src.UNICAEN_TBL_SRC_ID IS NULL
        ";

        foreach ($this->cols as $col) {
            $sql .= "\nOR dest.$col <> src.$col OR (dest.$col IS NULL AND src.$col IS NOT NULL) OR (dest.$col IS NOT NULL AND src.$col IS NULL)";
        }

        return $sql;
    }
}