<?php
include_once ("../lib/common.inc.php");
$query = 'update contractnotice set "parentCN" = null where "parentCN" = \'0\'';
$query = $conn->prepare($query);
        $query->execute();
$query = 'SELECT c."CNID",c."parentCN",p."childCN" FROM contractnotice as c  LEFT OUTER JOIN contractnotice as p on c."parentCN" = p."CNID"
WHERE
c."parentCN" IS NOT NULL AND p."childCN" IS NULL ';
$query = $conn->prepare($query);
        $query->execute();
                databaseError($conn->errorInfo());
foreach ($query->fetchAll() as $row) {
    if ($row['parentCN'] != 0) {
  $conn->exec('UPDATE contractnotice SET "childCN" = \'' .
                         $row['CNID'] . '\' where "CNID" = \'' .
                         $row['parentCN'] . '\';');
echo 'UPDATE contractnotice SET "childCN" = \'' .
                         $row['CNID'] . '\' 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" IS NOT NULL
)
AND "childCN" IS NULL
GROUP BY "parentCN" having count(*) > 1';
$query = $conn->prepare($query);
        $query->execute();
                databaseError($conn->errorInfo());
foreach ($query->fetchAll() as $row) {
        $cnids = explode(",",str_replace(Array("{","}"),"",$row['array_agg']));
        $last_cnid = array_pop($cnids);
        foreach ($cnids as $cnid) {
  $conn->exec('UPDATE contractnotice SET "childCN" = \'' .
                         $last_cnid . '\' where "CNID" = \'' .
                         $cnid . '\';');
                        
    $errors = $conn->errorInfo();
              
              if ($errors[1] == 7 || $errors[1] ==0)
                  echo $cnid . " linked to latest child " . $last_cnid .
  "<br>\n";
  else print_r($errors);
}
}
?>