new stats box
[contractdashboard.git] / admin / linkAmendments.php
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
67
<?php
include_once ("../lib/common.inc.php");
if (php_sapi_name() != "cli") {
    
//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);
}
}
 
?>