<?php
include_once ("../lib/common.inc.php");
$query = 'SELECT c."CNID",c."parentCN",c."amendmentReason",p."childCN" FROM contractnotice as c  LEFT OUTER JOIN contractnotice as p on c."parentCN" = p."CNID"
WHERE
c."parentCN" > 0 AND p."childCN" IS NULL ';
$query = $conn->prepare($query);
        $query->execute();
        if (!$query) {
                databaseError($conn->errorInfo());
        }
foreach ($query->fetchAll() as $row) {
  $conn->exec('UPDATE contractnotice SET "childCN" = \'' .
                         $row['CNID'] . '\', "amendmentReason" = \'' .
                         $row['amendmentReason'] . '\' where "CNID" = \'' .
                         $row['parentCN'] . '\';');
                         //echo 'UPDATE contractnotice SET "childCN" = \'' .
                         $row['CNID'] . '\', "amendmentReason" = \'' .
                         $row['amendmentReason'] . '\' where "CNID" = \'' .
                         $row['parentCN'] . '\';';
    $errors = $conn->errorInfo();
              
              if ($errors[1] == 7 || $errors[1] ==0)
                  echo $row['CNID'] . " linked to parent " . $row['parentCN'] .
  "<br>\n";
  else print_r($errors);
 
}
// also need to eliminate CN 100528/100529 - check for double parent CNs with no childCN, latest sequent CN id keeps childCN = 0
$query = 'SELECT "parentCN", array_agg("CNID"), count(*) from contractnotice WHERE "parentCN" IN
(
      SELECT "parentCN" 
      FROM contractnotice
      GROUP BY "parentCN" 
      HAVING COUNT(*) > 1 
      AND "parentCN" != 0
)
AND "childCN" = 0
GROUP BY "parentCN" having count(*) > 1';
?>