Reformatted contract view page
[contractdashboard.git] / admin / import.php
blob:a/admin/import.php -> blob:b/admin/import.php
<?php <?php
include_once("../lib/common.inc.php");  
$path = 'data/'; include_once ("../lib/common.inc.php");
if ($_REQUEST["fname"] == "") {  
echo "Get files from: https://www.tenders.gov.au/?event=public.reports.list<br>"; $contractNoticeFields = array(
$dhandle = opendir($path); "importFile",
// define an array to hold the files "agencyName",
$files = array(); "parentCN",
  "CNID",
if ($dhandle) { "publishDate",
// loop through all of the files "amendDate",
while (false !== ($fname = readdir($dhandle))) { "contractStart",
if (($fname != '.') && ($fname != '..')) { "contractEnd",
echo "<a href=\"import.php?fname=$fname\">$fname</a>&nbsp;" . filesize($path . $fname) . "&nbsp;" . date("c",filemtime($path . $fname)) . "<br/>"; "value",
} "description",
} "agencyID",
} "category",
} else { "procurementMethod",
$success = 0; "atmID",
$fname = $_REQUEST["fname"]; "SONID",
echo " ============== $fname ============== <br>"; "confidentialityContract",
flush(); "confidentialityContractReason",
$row = 1; "confidentialityOutputs",
$handle = fopen($path . $fname, "r"); "confidentialityOutputsReason",
//"t" mode string translates windows line breaks to unix "consultancy",
$datamapping0709 = array("Agency" => "agencyName", "Parent CN ID" => "parentCN", "CN ID" => "CNID", "Publish Date" => "publishDate", "Amendment Date" => "amendDate", "Status" => "", "StartDate" => "contractStart", "EndDate" => "contractEnd", "Value" => "value", "Description" => "description", "Agency Ref Id" => "agencyID", "Category" => "category", "Procurement Method" => "procurementMethod", "ATM ID" => "atmID", "SON ID" => "SONID", "Confidentiality - Contract" => "confidentialityContract", "Confidentiality - Contract Reason(s)" => "confidentialityContractReason", "Confidentiality - Outputs" => "confidentialityOutputs", "Confidentiality - Outputs Reason(s)" => "confidentialityOutputsReason", "Consultancy" => "consultancy", "Consultancy Reason(s)" => "consultancyReason", "Amendment Reason" => "amendmentReason", "Supplier Name" => "supplierName", "Supplier Address" => "supplierAddress", "Supplier City" => "supplierCity", "Supplier Postcode" => "supplierPostcode", "Supplier Country" => "supplierCountry", "Supplier ABNExempt" => "supplierABNExempt", "Supplier ABN" => "supplierABN", "Agency Branch" => "contactBranch", "Agency Divison" => "contactDivision", "Agency Postcode" => "contactPostcode", "" => ""); "consultancyReason",
$headers; "amendmentReason",
$contractNoticeFields = array("agencyName", "parentCN", "CNID", "publishDate", "amendDate", "contractStart", "contractEnd", "value", "description", "agencyID", "category", "procurementMethod", "atmID", "SONID", "confidentialityContract", "confidentialityContractReason", "confidentialityOutputs", "confidentialityOutputsReason", "consultancy", "consultancyReason", "amendmentReason", "supplierName", "supplierAddress", "supplierCity", "supplierPostcode", "supplierCountry", "supplierABNExempt", "supplierABN", "contactBranch", "contactDivision", "contactPostcode"); "supplierName",
$agencyFields = array("agencyName"); "supplierAddress",
$supplierFields = array("supplierName", "supplierAddress", "supplierCity", "supplierPostcode", "supplierCountry", "supplierABNExempt", "supplierABN"); "supplierCity",
  "supplierPostcode",
while (($data = fgetcsv($handle, 1000, "\t")) !== false) { "supplierCountry",
$num = count($data); "supplierABNExempt",
  "supplierABN",
if ($row == 3) { "contactBranch",
$headers = $data; "contactDivision",
} elseif ($row > 3) { "contactPostcode"
if ($num > count($datamapping0709)) { );
die("<font color=red>Error in data import; data mapping fields out of bounds or changed</font><br>" . $fname . print_r($data)); $agencyFields = array(
} "agencyName"
$firstCN = false; );
$firstSupplier = true; $supplierFields = array(
$firstAgency = true; "supplierName",
$contractNoticeInsert = "INSERT INTO contractnotice (importFile, " . implode(" , ", $contractNoticeFields) . ") VALUES ( \"$fname\" "; "supplierAddress",
$supplierInsert = "INSERT INTO supplierdetails (" . implode(" , ", $supplierFields) . ") VALUES ("; "supplierCity",
$agencyInsert = "INSERT INTO agency (" . implode(" , ", $agencyFields) . ") VALUES ("; "supplierPostcode",
  "supplierCountry",
$keys = array_keys($datamapping0709); "supplierABNExempt",
for ($c = 0; $c < $num; $c++) { "supplierABN"
$data[$c] = trim($data[$c], "="); );
$data[$c] = trim($data[$c], "\""); $contractNoticeInsertQ = 'INSERT INTO contractnotice ("' . implode('" , "', $contractNoticeFields) . '") VALUES ( ';
  foreach ($contractNoticeFields as $key => $f) {
if (in_array(($datamapping0709[$headers[$c]]), $contractNoticeFields)) { $contractNoticeInsertQ.= ($key == 0 ? "" : ", ") . "?";
if (($datamapping0709[$headers[$c]]) == "parentCN" || ($datamapping0709[$headers[$c]]) == "CNID") { }
$data[$c] = substr($data[$c], 2); // take off the "CN" prefix $contractNoticeInsertQ.= ");";
$data[$c] = str_replace("-A","00",$data[$c]); // make amendments really big numbers $contractNoticeInsertQ = $conn->prepare($contractNoticeInsertQ);
if ($data[$c] > 0) {  
$contractNoticeInsert .= ($firstCN ? "" : ",") . mysql_real_escape_string($data[$c]); $supplierInsertQ = 'INSERT INTO supplierdetails ("' . implode('" , "', $supplierFields) . '") VALUES ( ';
} else { foreach ($supplierFields as $key => $f) {
$contractNoticeInsert .= ($firstCN ? "" : ",") . "''"; $supplierInsertQ.= ($key == 0 ? "" : ", ") . "?";
} }
} elseif (($datamapping0709[$headers[$c]]) == "amendDate" || ($datamapping0709[$headers[$c]]) == "publishDate" || ($datamapping0709[$headers[$c]]) == "contractStart" || ($datamapping0709[$headers[$c]]) == "contractEnd") { $supplierInsertQ.= ");";
$contractNoticeInsert .= ($firstCN ? "" : ",") . "'" . date('Y-m-d H:i:s', strtotime($data[$c])) . "'"; $supplierInsertQ = $conn->prepare($supplierInsertQ);
} else {  
if (strstr("\" =", $data[$c] > 0)) { $agencyInsertQ = 'INSERT INTO agency_nametoabn ("' . implode('" , "', $agencyFields) . '") VALUES ( ';
die("Invalid Description field" . $contractNoticeInsert); foreach ($agencyFields as $key => $f) {
} $agencyInsertQ.= ($key == 0 ? "" : ", ") . "?";
$contractNoticeInsert .= ($firstCN ? "" : ",") . "'" . mysql_real_escape_string($data[$c]) . "'"; }
} $agencyInsertQ.= ");";
$firstCN = false; $agencyInsertQ = $conn->prepare($agencyInsertQ);
}  
if (in_array(($datamapping0709[$headers[$c]]), $supplierFields)) { function processFile($fpath, $tablename) {
$supplierInsert .= ($firstSupplier ? "" : ",") . "'" . mysql_real_escape_string($data[$c]) . "'"; global $conn, $contractNoticeQ, $supplierInsertQ, $agencyInsertQ;
$firstSupplier = false; $row = 1;
} $handle = fopen($fpath, "r");
if (in_array(($datamapping0709[$headers[$c]]), $agencyFields)) { //"t" mode string translates windows line breaks to unix
$agencyInsert .= ($firstAgency ? "" : ",") . "'" . mysql_real_escape_string($data[$c]) . "'"; $datamapping0711 = array(
$firstAgency = false; "Agency" => "agencyName",
} "Parent CN ID" => "parentCN",
} "CN ID" => "CNID",
flush(); "Publish Date" => "publishDate",
$contractNoticeInsert .= ");"; "Amendment Date" => "amendDate",
$supplierInsert .= ");"; "Status" => "",
$agencyInsert .= ");"; "StartDate" => "contractStart",
  "EndDate" => "contractEnd",
$result = mysql_query($contractNoticeInsert); "Value" => "value",
if ($result) "Description" => "description",
$success++; "Agency Ref Id" => "agencyID",
elseif (strpos(mysql_error(), "Duplicate entry") === false) "Category" => "category",
echo $data[2] . " failed CN insert.<br>" . mysql_error() . " <br> $contractNoticeInsert <br> $row <br><br>\n"; "Procurement Method" => "procurementMethod",
  "ATM ID" => "atmID",
$result = mysql_query($agencyInsert); "SON ID" => "SONID",
if ($result) "Confidentiality - Contract" => "confidentialityContract",
$success++; "Confidentiality - Contract Reason(s)" => "confidentialityContractReason",
elseif (strpos(mysql_error(), "Duplicate entry") === false) "Confidentiality - Outputs" => "confidentialityOutputs",
echo $data[2] . " failed agency insert.<br>" . mysql_error() . " <br> $agencyInsert <br>$row <br><br>\n"; "Confidentiality - Outputs Reason(s)" => "confidentialityOutputsReason",
  "Consultancy" => "consultancy",
$result = mysql_query($supplierInsert); "Consultancy Reason(s)" => "consultancyReason",
if ($result) "Amendment Reason" => "amendmentReason",
$success++; "Supplier Name" => "supplierName",
elseif (strpos(mysql_error(), "Duplicate entry") === false) "Supplier Address" => "supplierAddress",
echo $data[2] . " failed supplier insert.<br>" . mysql_error() . " <br> $supplierInsert <br>$row <br><br>\n"; "Supplier City" => "supplierCity",
flush(); "Supplier Postcode" => "supplierPostcode",
  "Supplier Country" => "supplierCountry",
//echo "<hr>\n"; "Supplier ABNExempt" => "supplierABNExempt",
} "Supplier ABN" => "supplierABN",
  "Agency Branch" => "contactBranch",
$row++; "Agency Divison" => "contactDivision",
} "Agency Postcode" => "contactPostcode",
echo "<br> $success records successfully created"; "" => ""
flush(); );
fclose($handle); $headers;
}  
  while (($data = fgetcsv($handle, 1000, "\t")) !== false) {
  $num = count($data);
  if ($row == 3) {
  $headers = $data;
  } elseif ($row > 3) {
  if ($num > count($datamapping0711)) {
  die("<font color=red>Error in data import; data mapping fields out of bounds or changed</font><br>" . $fname . print_r($data));
  }
  $contractNoticeInsert = Array();
  $supplierInsert = Array();
  $agencyInsert = Array();
  $contractNoticeInsert[] = $fpath;
  $keys = array_keys($datamapping0711);
  for ($c = 0; $c < $num; $c++) {
  $data[$c] = trim($data[$c], "=");
  $data[$c] = trim($data[$c], "\"");
  if ($tablename == "contractnotice") {
  if (in_array(($datamapping0711[$headers[$c]]), $contractNoticeFields)) {
  if (($datamapping0711[$headers[$c]]) == "parentCN" || ($datamapping0711[$headers[$c]]) == "CNID") {
  $data[$c] = substr($data[$c], 2); // take off the "CN" prefix
  if ($data[$c] > 0) {
  $contractNoticeInsert[] = $data[$c];
  } else {
  $contractNoticeInsert[] = 0;
  }
  } elseif (($datamapping0711[$headers[$c]]) == "supplierABN") {
  if ($data[$c] > 0) {
  $contractNoticeInsert[] = $data[$c];
  } else {
  $contractNoticeInsert[] = null;
  }
  } elseif (($datamapping0711[$headers[$c]]) == "amendDate" || ($datamapping0711[$headers[$c]]) == "publishDate" || ($datamapping0711[$headers[$c]]) == "contractStart" || ($datamapping0711[$headers[$c]]) == "contractEnd") {
  $contractNoticeInsert[] = date('Y-m-d H:i:s', strtotime($data[$c]));
  } else {
  if (strstr("\" =", $data[$c] > 0)) {
  die("Invalid Description field" . $contractNoticeInsert);
  }
  $colvalue = preg_replace('/[^[:print:]]/', '', utf8_encode($data[$c]));
   
  $contractNoticeInsert[] = $colvalue;
  }
  }
  } else if ($tablename == "supplierdetails") {
  if (in_array(($datamapping0711[$headers[$c]]), $supplierFields)) {
  if (($datamapping0711[$headers[$c]]) == "supplierABN") {
  if ($data[$c] > 0) {
  $contractNoticeInsert[] = $data[$c];
  } else {
  $contractNoticeInsert[] = 0;
  }
  } else {
  $supplierInsert[] = $data[$c];
  }
  }
  } else if ($tablename == "agency") {
  if (in_array(($datamapping0711[$headers[$c]]), $agencyFields)) {
  $agencyInsert[] = $data[$c];
  }
  }
  }
  flush();
  if ($tablename == "contractnotice") {
  $contractNoticeInsertQ->execute($contractNoticeInsert);
  $errors = $conn->errorInfo();
  if ($errors[1] == 7 && strpos($errors[2], "duplicate key")) {
   
  } elseif ($errors[1] == 0) {
  $success++;
  } else {
  foreach ($contractNoticeFields as $key => $cnf) {
  echo var_dump($contractNoticeInsert[$key]) . $cnf . "<br>";
  }
  echo $data[2] . " failed CN insert.<br>" . print_r($errors, true) . "<br> row $row <br><br>\n";
  }
  } else if ($tablename == "supplierdetails") {
  $supplierInsertQ->execute($supplierInsert);
  $errors = $conn->errorInfo();
  if ($errors[1] == 7 && strpos($errors[2], "duplicate key")) {
   
  } elseif ($errors[1] == 0) {
  $success++;
  } else {
  echo $data[2] . " failed supplier insert.<br>" . print_r($errors, true) . " <br> " . print_r($supplierInsert, true) . "<br> $row <br><br>\n";
  }
  } else if ($tablename == "agency") {
  $agencyInsertQ->execute($agencyInsert);
  $errors = $conn->errorInfo();
  if ($errors[1] == 7 && strpos($errors[2], "duplicate key")) {
   
  } elseif ($errors[1] == 0) {
  $success++;
  } else {
  echo $data[2] . " failed agency insert.<br>" . print_r($errors, true) . " <br> " . print_r($agencyInsert, true) . "<br> $row <br><br>\n";
  }
  }
  flush();
  //echo "<hr>\n";
  }
  $row++;
  }
  fclose($handle);
  $contractNoticeInsertQ->closeCursor();
  $supplierInsertQ->closeCursor();
  $agencyInsertQ->closeCursor();
   
  return $success;
  }
   
  $path = 'data/';
  if ($_REQUEST["fname"] == "") {
  echo "Get files from: https://www.tenders.gov.au/?event=public.reports.list<br>";
  $dhandle = opendir($path);
  // define an array to hold the files
  $files = array();
  if ($dhandle) {
  // loop through all of the files
  while (false !== ($fname = readdir($dhandle))) {
  if (($fname != '.') && ($fname != '..')) {
  echo "<a href=\"import.php?fname=$fname\">$fname</a>&nbsp;" . filesize($path . $fname) . "&nbsp;" . date("c", filemtime($path . $fname)) . "<br/>";
  }
  }
  }
  } else {
  $success = 0;
  $fname = $_REQUEST["fname"];
  echo " ============== $fname ============== <br>";
  flush();
  $success+= processFile($path . $fname, "contractnotice");
  $success+= processFile($path . $fname, "agency");
  $success+= processFile($path . $fname, "supplier");
  echo "<br> $success records successfully created";
   
  flush();
  // run post import data processing
  // cn
  echo "link amend<br>";
  include ("linkAmendments.php");
  echo "update UNSPSC<br>";
  include ("updateUNSPSC.php");
  // agency
  //include ("setAgencyStatus.php");
  //include ("setAgencyURLABN.php");
  }
?> ?>