gistfile1.php

<?php
  // Update cultures for organizational elements (Document types)
  foreach (array(
    '(A) Agendas' => '(A) Ordres du jour',
    '(D) Documents' => '(D) Documents',
    '(M) Memoranda' => '(M) Memoranda',
    '(N) Notes' => '(N) Notes',
    '(R) Records' => '(R) Comptes rendus',
    '(VR) Verbatim Records' => '(VR) Comptes rendus textuels',
    '(WP) Working Papers' => '(WP) Documents de travail') as $en => $fr)
  {
    // Get elements by title in english culture
    $sql = sprintf("SELECT t1.id, lft, rgt
      FROM %s t1
      INNER JOIN %s t2
      ON t1.id = t2.id
      WHERE title = ?
      AND culture = ?;", QubitInformationObject::TABLE_NAME, QubitInformationObjectI18n::TABLE_NAME);

    $rows = QubitPdo::fetchAll($sql, array($en, 'en'));

    foreach ($rows as $description)
    {
      // Get different cultures among the descendants
      $sql = sprintf("SELECT DISTINCT culture
      FROM %s t1
      INNER JOIN %s t2
      ON t1.id = t2.id
      WHERE lft > ?
      AND rgt < ?;", QubitInformationObject::TABLE_NAME, QubitInformationObjectI18n::TABLE_NAME);

      $cultures = QubitPdo::fetchAll($sql, array($description->lft, $description->rgt));

      // There are only french and english culture in the DB
      // So, if there is more than one culture among the descendants
      // the French row is added
      if(count($cultures) > 1)
      {
        $sql = sprintf("SELECT id
          FROM %s
          WHERE id = ?
          AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

        // Only if it doesn't exist yet
        if (count(QubitPdo::fetchAll($sql, array($description->id, 'fr'))) != 1)
        {
          $sql = sprintf("INSERT INTO %s
            (id, culture, title)
            VALUES (?, ?, ?);", QubitInformationObjectI18n::TABLE_NAME);

          QubitPdo::modify($sql, array($description->id, 'fr', $fr));
        }
      }
      // If there are only French descendants
      else if (count($cultures) == 1 && $cultures[0]->culture == 'fr')
      {
        // Modify the source_culture of the element to French
        $sql = sprintf("UPDATE %s
          SET source_culture = ?
          WHERE id = ?;", QubitInformationObject::TABLE_NAME);

        QubitPdo::modify($sql, array('fr', $description->id));

        // Change the i18n English row to a French row
        $sql = sprintf("UPDATE %s
          SET culture = ?, title = ?
          WHERE id = ?
          AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

        QubitPdo::modify($sql, array('fr', $fr, $description->id, 'en'));
      }
      // If there are only English descendants
      else if (count($cultures) == 1 && $cultures[0]->culture == 'en')
      {
        // Make sure that the source_culture of the element is English
        $sql = sprintf("UPDATE %s
          SET source_culture = ?
          WHERE id = ?;", QubitInformationObject::TABLE_NAME);

        QubitPdo::modify($sql, array('en', $description->id));

        // Remove the French row if exists
        $sql = sprintf("DELETE FROM %s
          WHERE id = ?
          AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

        QubitPdo::modify($sql, array($description->id, 'fr'));
      }
    }
  }

  // Update cultures for organizational elements (Years)
  // Almost the same as for the other elements
  $sql = sprintf("SELECT t1.id, title, lft, rgt
    FROM %s t1
    INNER JOIN %s t2
    ON t1.id = t2.id
    WHERE title REGEXP ?
    AND culture = ?;", QubitInformationObject::TABLE_NAME, QubitInformationObjectI18n::TABLE_NAME);

  $rows = QubitPdo::fetchAll($sql, array('^[0-9]{4}$', 'en'));

  foreach ($rows as $description)
  {
    $sql = sprintf("SELECT DISTINCT culture
      FROM %s t1
      INNER JOIN %s t2
      ON t1.id = t2.id
      WHERE lft > ?
      AND rgt < ?;", QubitInformationObject::TABLE_NAME, QubitInformationObjectI18n::TABLE_NAME);

    $cultures = QubitPdo::fetchAll($sql, array($description->lft, $description->rgt));

    if (count($cultures) > 1)
    {
      $sql = sprintf("SELECT id
        FROM %s
        WHERE id = ?
        AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

      if (count(QubitPdo::fetchAll($sql, array($description->id, 'fr'))) != 1)
      {
        $sql = sprintf("INSERT INTO %s
          (id, culture, title)
          VALUES (?, ?, ?);", QubitInformationObjectI18n::TABLE_NAME);

        QubitPdo::modify($sql, array($description->id, 'fr', $description->title));
      }
    }
    else if (count($cultures) == 1 && $cultures[0]->culture == 'fr')
    {
      $sql = sprintf("UPDATE %s
        SET source_culture = ?
        WHERE id = ?;", QubitInformationObject::TABLE_NAME);

      QubitPdo::modify($sql, array('fr', $description->id));

      $sql = sprintf("UPDATE %s
        SET culture = ?
        WHERE id = ?
        AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

      QubitPdo::modify($sql, array('fr', $description->id, 'en'));
    }
    else if (count($cultures) == 1 && $cultures[0]->culture == 'en')
    {
      $sql = sprintf("UPDATE %s
        SET source_culture = ?
        WHERE id = ?;", QubitInformationObject::TABLE_NAME);

      QubitPdo::modify($sql, array('en', $description->id));

      $sql = sprintf("DELETE FROM %s
        WHERE id = ?
        AND culture = ?;", QubitInformationObjectI18n::TABLE_NAME);

      QubitPdo::modify($sql, array($description->id, 'fr'));
    }
  }
?>