projects / contractdashboard.git / blobdiff
search:
summary | shortlog | log | commit | commitdiff | tree
unified | plain
Depreciate MySQL and GD image graphs
[contractdashboard.git] / admin / import.php
blob:a/admin/import.php -> blob:b/admin/import.php
<?php <?php
include_once("../lib/common.inc.php");  
  include_once ("../lib/common.inc.php");
function processFile($fpath,$tablename){  
global $conn; $contractNoticeFields = array(
$row = 1; "importFile",
$handle = fopen($fpath, "r"); "agencyName",
//"t" mode string translates windows line breaks to unix "parentCN",
$datamapping0711 = 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", "" => ""); "CNID",
$headers; "publishDate",
$contractNoticeFields = array("importFile", "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"); "amendDate",
$agencyFields = array("agencyName"); "contractStart",
$supplierFields = array("supplierName", "supplierAddress", "supplierCity", "supplierPostcode", "supplierCountry", "supplierABNExempt", "supplierABN"); "contractEnd",
  "value",
if ($tablename == "contractnotice") { "description",
$contractNoticeInsertQ = 'INSERT INTO contractnotice ("' . implode('" , "', $contractNoticeFields) . '") VALUES ( '; "agencyID",
foreach ($contractNoticeFields as $key => $f) { "category",
$contractNoticeInsertQ .= ($key == 0 ? "" : ", ")."?"; "procurementMethod",
} "atmID",
$contractNoticeInsertQ .= ");"; "SONID",
$contractNoticeInsertQ = $conn->prepare($contractNoticeInsertQ); "confidentialityContract",
  "confidentialityContractReason",
  "confidentialityOutputs",
} else if ($tablename == "supplierdetails") { "confidentialityOutputsReason",
$supplierInsertQ = 'INSERT INTO supplierdetails ("' . implode('" , "', $supplierFields) . '") VALUES ( '; "consultancy",
foreach ($supplierFields as $key => $f) { "consultancyReason",
$supplierInsertQ .= ($key == 0 ? "" : ", ")."?"; "amendmentReason",
} "supplierName",
$supplierInsertQ .= ");"; "supplierAddress",
$supplierInsertQ = $conn->prepare($supplierInsertQ); "supplierCity",
  "supplierPostcode",
} else if ($tablename == "agency") { "supplierCountry",
$agencyInsertQ = 'INSERT INTO agency ("' . implode('" , "', $agencyFields) . '") VALUES ( '; "supplierABNExempt",
foreach ($agencyFields as $key => $f) { "supplierABN",
$agencyInsertQ .= ($key == 0 ? "" : ", ")."?"; "contactBranch",
} "contactDivision",
$agencyInsertQ .= ");"; "contactPostcode"
$agencyInsertQ = $conn->prepare($agencyInsertQ); );
} $agencyFields = array(
  "agencyName"
while (($data = fgetcsv($handle, 1000, "\t")) !== false) { );
$num = count($data); $supplierFields = array(
  "supplierName",
if ($row == 3) { "supplierAddress",
$headers = $data; "supplierCity",
} elseif ($row > 3) { "supplierPostcode",
if ($num > count($datamapping0711)) { "supplierCountry",
die("<font color=red>Error in data import; data mapping fields out of bounds or changed</font><br>" . $fname . print_r($data)); "supplierABNExempt",
} "supplierABN"
$contractNoticeInsert = Array(); );
$supplierInsert = Array(); $contractNoticeInsertQ = 'INSERT INTO contractnotice ("' . implode('" , "', $contractNoticeFields) . '") VALUES ( ';
$agencyInsert = Array(); foreach ($contractNoticeFields as $key => $f) {
$contractNoticeInsert[] = $fname; $contractNoticeInsertQ.= ($key == 0 ? "" : ", ") . "?";
$keys = array_keys($datamapping0711); }
for ($c = 0; $c < $num; $c++) { $contractNoticeInsertQ.= ");";
$data[$c] = trim($data[$c], "="); $contractNoticeInsertQ = $conn->prepare($contractNoticeInsertQ);
$data[$c] = trim($data[$c], "\"");  
if ($tablename == "contractnotice") { $supplierInsertQ = 'INSERT INTO supplierdetails ("' . implode('" , "', $supplierFields) . '") VALUES ( ';
if (in_array(($datamapping0711[$headers[$c]]), $contractNoticeFields)) { foreach ($supplierFields as $key => $f) {
if (($datamapping0711[$headers[$c]]) == "parentCN" || ($datamapping0711[$headers[$c]]) == "CNID") { $supplierInsertQ.= ($key == 0 ? "" : ", ") . "?";
$data[$c] = substr($data[$c], 2); // take off the "CN" prefix }
$data[$c] = str_replace("-A","00",$data[$c]); // make amendments really big numbers $supplierInsertQ.= ");";
if ($data[$c] > 0) { $supplierInsertQ = $conn->prepare($supplierInsertQ);
$contractNoticeInsert[] = $data[$c];  
} else { $agencyInsertQ = 'INSERT INTO agency_nametoabn ("' . implode('" , "', $agencyFields) . '") VALUES ( ';
$contractNoticeInsert[] = 0; foreach ($agencyFields as $key => $f) {
} $agencyInsertQ.= ($key == 0 ? "" : ", ") . "?";
} 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])); $agencyInsertQ.= ");";
} else { $agencyInsertQ = $conn->prepare($agencyInsertQ);
if (strstr("\" =", $data[$c] > 0)) {  
die("Invalid Description field" . $contractNoticeInsert); function processFile($fpath, $tablename) {
} global $conn, $contractNoticeQ, $supplierInsertQ, $agencyInsertQ;
$contractNoticeInsert[] = $data[$c]; $row = 1;
} $handle = fopen($fpath, "r");
} //"t" mode string translates windows line breaks to unix
  $datamapping0711 = array(
} else if ($tablename == "supplierdetails") { "Agency" => "agencyName",
if (in_array(($datamapping0711[$headers[$c]]), $supplierFields)) { "Parent CN ID" => "parentCN",
$supplierInsert[] = $data[$c]; "CN ID" => "CNID",
} "Publish Date" => "publishDate",
  "Amendment Date" => "amendDate",
} else if ($tablename == "agency") { "Status" => "",
if (in_array(($datamapping0711[$headers[$c]]), $agencyFields)) { "StartDate" => "contractStart",
$agencyInsert[] = $data[$c]; "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",
  "" => ""
  );
  $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>";
flush(); include ("linkAmendments.php");
if ($tablename == "contractnotice") { echo "update UNSPSC<br>";
$contractNoticeInsertQ->execute($contractNoticeInsert); include ("updateUNSPSC.php");
$errors = $conn->errorInfo(); // agency
  //include ("setAgencyStatus.php");
if ($errors[1] == 7 || $errors[1] ==0) //include ("setAgencyURLABN.php");
$success++; }
else  
echo $data[2] . " failed CN insert.<br>" . print_r($errors,true)  
. " <br> ". print_r($contractNoticeInsert,true) ."<br> $row <br><br>\n";  
   
   
} else if ($tablename == "supplierdetails") {  
$supplierInsertQ->execute($supplierInsert);  
$errors = $conn->errorInfo();  
   
if ($errors[1] == 7 || $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";  
   
   
} else if ($tablename == "agency") {  
$agencyInsertQ->execute($agencyInsert);  
$errors = $conn->errorInfo();  
   
if ($errors[1] == 7 || $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";  
   
}  
   
   
   
flush();  
   
//echo "<hr>\n";  
}  
   
$row++;  
}  
   
fclose($handle);  
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