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 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | <?php if (php_sapi_name() != "cli") { include_once ("../lib/common.inc.php"); auth(); $query = 'update contractnotice set "parentCN" = null where "parentCN" = \'0\''; $query = $conn->prepare($query); $query->execute(); $query = 'update contractnotice set "supplierABN" = null where "supplierABN" = \'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); } } } ?> |