1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | <?php if (php_sapi_name() != "cli") { include_once ("../lib/common.inc.php"); auth(); $query = 'update contractnotice set "parentCN" = null where "parentCN" = \'0\''; $result = $conn->prepare($query); $result->execute(); $query = 'update contractnotice set "childCN" = null where "childCN" = \'0\''; $result = $conn->prepare($query); $result->execute(); $query = 'select "CNID","parentCN" from contractnotice where char_length("CNID") > 6 and "CNID" like \'%00_\' and "parentCN" is not null'; // "CNID" not like '%-A%' //$query = 'select "CNID","parentCN" from contractnotice where char_length("CNID") > 8 and "CNID" like \'%00__\' and "parentCN" is not null'; $result = $conn->prepare($query); $result->execute(); foreach ($result->fetchAll() as $record) { $oldCN = $record['CNID']; $parentCN = substr($oldCN, 0, -3); //$parentCN = substr($oldCN, 0, -4); if ($parentCN == $record['parentCN']) { $newCN = $parentCN . "-A" . substr($oldCN, -1); //$newCN = $parentCN . "-A" . substr($oldCN, -2); $updateresult = $conn->exec('UPDATE contractnotice SET "CNID" = \'' . $newCN . '\' where "CNID" = \'' . $oldCN . '\';'); $errors = $conn->errorInfo(); if ($errors[1] == 7) { echo "$oldCN => $newCN (from parent CN $parentCN) BUT already exists so deleting this record<br>\n"; $updateresult = $conn->exec('delete from contractnotice where "CNID" = \'' . $oldCN . '\';'); }else if ($errors[1] == 0) { echo "$oldCN => $newCN (from parent CN $parentCN) <br>\n"; }else print_r($errors); } else { echo "parent CN unexpected - $oldCN doesn't look like child of {$record['parentCN']}, rather the ID suggests child of $parentCN <br>\n"; } } } |