updated update script and merged lobbylens admin scripts
[contractdashboard.git] / admin / import.php
blob:a/admin/import.php -> blob:b/admin/import.php
<?php <?php
include_once("../lib/common.inc.php"); if (php_sapi_name() != "cli") {
$path = '../data/';  
  include_once ("../lib/common.inc.php");
  auth();
  $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"
  );
   
  $contractNoticeInsertQ = 'INSERT INTO contractnotice ("' . implode('" , "', $contractNoticeFields) . '") VALUES ( ';
  foreach ($contractNoticeFields as $key => $f) {
  $contractNoticeInsertQ.= ($key == 0 ? "" : ", ") . "?";
  }
  $contractNoticeInsertQ.= ");";
  $contractNoticeInsertQ = $conn->prepare($contractNoticeInsertQ);
   
  function processFile($fpath) {
  global $conn, $contractNoticeFields, $contractNoticeInsertQ;
  $row = 1;
  $handle = fopen($fpath, "r");
  //"t" mode string translates windows line breaks to unix
  $datamapping0712 = 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",
  "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",
  "" => ""
  );
   
   
  while (($data = fgetcsv($handle, 1000, "\t")) !== false) {
  $num = count($data);
  if ($row == 3) {
  $headers = $data;
  } elseif ($row > 3) {
  if ($num > count($datamapping0712)) {
  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($datamapping0712);
  for ($c = 0; $c < $num; $c++) {
  $data[$c] = trim($data[$c], "=");
  $data[$c] = trim($data[$c], "\"");
  if (in_array(($datamapping0712[$headers[$c]]), $contractNoticeFields)) {
  if (($datamapping0712[$headers[$c]]) == "parentCN" || ($datamapping0712[$headers[$c]]) == "CNID") {
  $data[$c] = substr($data[$c], 2); // take off the "CN" prefix
  if ($data[$c] > 0 && $data[$c] != '0') {
  $contractNoticeInsert[] = $data[$c];
  } else {
  $contractNoticeInsert[] = null;
  }
  } elseif (($datamapping0712[$headers[$c]]) == "supplierABN") {
  if ($data[$c] > 0 && $data[$c] != '0') {
  $contractNoticeInsert[] = $data[$c];
  } else {
  $contractNoticeInsert[] = null;
  }
  } elseif (($datamapping0712[$headers[$c]]) == "amendDate" || ($datamapping0712[$headers[$c]]) == "publishDate" || ($datamapping0712[$headers[$c]]) == "contractStart" || ($datamapping0712[$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;
  }
  }
  }
  flush();
  $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";
  }
   
  flush();
  //echo "<hr>\n";
  }
  $row++;
  }
  fclose($handle);
  $contractNoticeInsertQ->closeCursor();
   
  return $success;
  }
   
  $path = 'data/';
if ($_REQUEST["fname"] == "") { if ($_REQUEST["fname"] == "") {
echo "Get files from: https://www.tenders.gov.au/?event=public.reports.list<br>"; echo "Get files from: https://www.tenders.gov.au/?event=public.reports.list<br>";
$dhandle = opendir($path); $dhandle = opendir($path);
// define an array to hold the files // define an array to hold the files
$files = array(); $files = array();
  if ($dhandle) {
if ($dhandle) { // loop through all of the files
// loop through all of the files while (false !== ($fname = readdir($dhandle))) {
while (false !== ($fname = readdir($dhandle))) { if (($fname != '.') && ($fname != '..')) {
if (($fname != '.') && ($fname != '..')) { $files[date("c", filemtime($path . $fname)) . md5($fname)] = $fname;
echo "<a href=\"import.php?fname=$fname\">$fname</a>".filesize($path.$fname)."<br/>"; }
} }
} }
} ksort($files);
  foreach ($files as $date => $fname) {
  echo "<a href=\"importdatagov.php?fname=$fname\">$fname</a>&nbsp;" . filesize($path . $fname) . "&nbsp;" . $date . "<br/>";
  }
} else { } else {
$success =0; $success = 0;
$fname = $_REQUEST["fname"]; $fname = $_REQUEST["fname"];
echo " ============== $fname ============== <br>"; echo " ============== $fname ============== <br>";
$row = 1; flush();
$handle = fopen($path.$fname, "r"); $success+= processFile($path . $fname, "contractnotice");
//"t" mode string translates windows line breaks to unix $success+= processFile($path . $fname, "agency");
$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" , "" => ""); $success+= processFile($path . $fname, "supplier");
$headers; echo "<br> $success records successfully created";
$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" );  
$agencyFields = Array("agencyName"); flush();
$supplierFields = Array("supplierName", "supplierAddress", "supplierCity", "supplierPostcode", "supplierCountry" , "supplierABNExempt", "supplierABN" ); // run post import data processing
  //
while (($data = fgetcsv($handle, 1000, "\t")) !== FALSE) { if ($success > 0) {
$num = count($data); $conn->exec("update datasets set \"lastUpdated\" = NOW() where title = 'Contract Notices'");
  echo "link amend<br>";
if ($row == 3) include ("linkAmendments.php");
{ echo "update UNSPSC<br>";
$headers = $data; include ("updateUNSPSC.php");
} }
else if ($row > 3) // cn
{  
if ($num > count($datamapping0709)) // agency
{ //include ("setAgencyStatus.php");
die ("<font color=red>Error in data import; data mapping fields out of bounds or changed</font><br>".$fname.print_r($data)); //include ("setAgencyURLABN.php");
} }
$firstCN= false;  
$firstSupplier= true;  
$firstAgency= true;  
$contractNoticeInsert = "INSERT INTO contractnotice (importFile, ".implode(" , ",$contractNoticeFields). ") VALUES ( \"$fname\" ";  
$supplierInsert = "INSERT INTO supplierdetails (".implode(" , ",$supplierFields). ") VALUES (";  
$agencyInsert = "INSERT INTO agency (".implode(" , ",$agencyFields). ") VALUES (";  
   
$keys = array_keys($datamapping0709);  
for ($c=0; $c < $num; $c++) {  
$data[$c] = trim($data[$c],"=");  
$data[$c] = trim($data[$c], "\"");  
   
if (in_array(($datamapping0709[$headers[$c]]),$contractNoticeFields))  
{  
if (($datamapping0709[$headers[$c]]) == "parentCN" || ($datamapping0709[$headers[$c]]) == "CNID")  
{  
$data[$c] = substr($data[$c],2);  
if ($data[$c] >0)  
{  
$contractNoticeInsert .= ($firstCN ? "" : ","). mysql_real_escape_string($data[$c]);  
} else  
{  
$contractNoticeInsert .= ($firstCN ? "" : ",")."''";  
}  
} else if (($datamapping0709[$headers[$c]]) == "amendDate" || ($datamapping0709[$headers[$c]]) == "publishDate" || ($datamapping0709[$headers[$c]]) == "contractStart" || ($datamapping0709[$headers[$c]]) == "contractEnd")  
{  
$contractNoticeInsert .= ($firstCN ? "" : ","). "'".date( 'Y-m-d H:i:s', strtotime($data[$c])). "'";  
} else  
{  
if (strstr("\" =",$data[$c] > 0))  
{  
die ("Invalid Description field" . $contractNoticeInsert);  
}  
$contractNoticeInsert .= ($firstCN ? "" : ",") . "'". mysql_real_escape_string($data[$c])."'";  
   
}  
$firstCN = false;  
}  
if (in_array(($datamapping0709[$headers[$c]]),$supplierFields))  
{  
$supplierInsert .= ($firstSupplier ? "" : ",") . "'". mysql_real_escape_string($data[$c])."'";  
$firstSupplier = false;  
}  
if (in_array(($datamapping0709[$headers[$c]]),$agencyFields))  
{  
$agencyInsert .= ($firstAgency ? "" : ",") . "'". mysql_real_escape_string($data[$c])."'";  
$firstAgency = false;  
}  
   
}  
$contractNoticeInsert .= ");";  
$supplierInsert .= ");";  
$agencyInsert .= ");";  
   
$result = mysql_query($contractNoticeInsert);  
if ($result) $success++;  
else if (strpos(mysql_error(),"Duplicate entry") === false) echo $data[2] . " failed CN insert.<br>". mysql_error() ." <br> $contractNoticeInsert <br><br>\n";  
   
$result = mysql_query($agencyInsert);  
if ($result) $success++;  
else if (strpos(mysql_error(),"Duplicate entry") === false) echo $data[2] . " failed agency insert.<br>". mysql_error() ." <br> $agencyInsert <br><br>\n";  
   
$result = mysql_query($supplierInsert);  
if ($result) $success++;  
else if (strpos(mysql_error(),"Duplicate entry") === false) echo $data[2] . " failed supplier insert.<br>". mysql_error() ." <br> $supplierInsert <br><br>\n";  
   
//echo "<hr>\n";  
}  
   
$row++;  
}  
echo "<br> $success records successfully created";  
fclose($handle);  
} }
?> ?>