--- a/admin/import.php +++ b/admin/import.php @@ -36,18 +36,7 @@ "contactDivision", "contactPostcode" ); -$agencyFields = array( - "agencyName" -); -$supplierFields = array( - "supplierName", - "supplierAddress", - "supplierCity", - "supplierPostcode", - "supplierCountry", - "supplierABNExempt", - "supplierABN" -); + $contractNoticeInsertQ = 'INSERT INTO contractnotice ("' . implode('" , "', $contractNoticeFields) . '") VALUES ( '; foreach ($contractNoticeFields as $key => $f) { $contractNoticeInsertQ.= ($key == 0 ? "" : ", ") . "?"; @@ -55,26 +44,12 @@ $contractNoticeInsertQ.= ");"; $contractNoticeInsertQ = $conn->prepare($contractNoticeInsertQ); -$supplierInsertQ = 'INSERT INTO supplierdetails ("' . implode('" , "', $supplierFields) . '") VALUES ( '; -foreach ($supplierFields as $key => $f) { - $supplierInsertQ.= ($key == 0 ? "" : ", ") . "?"; -} -$supplierInsertQ.= ");"; -$supplierInsertQ = $conn->prepare($supplierInsertQ); - -$agencyInsertQ = 'INSERT INTO agency_nametoabn ("' . implode('" , "', $agencyFields) . '") VALUES ( '; -foreach ($agencyFields as $key => $f) { - $agencyInsertQ.= ($key == 0 ? "" : ", ") . "?"; -} -$agencyInsertQ.= ");"; -$agencyInsertQ = $conn->prepare($agencyInsertQ); - -function processFile($fpath, $tablename) { - global $conn, $contractNoticeFields, $contractNoticeInsertQ, $supplierFields, $supplierInsertQ,$agencyFields, $agencyInsertQ; +function processFile($fpath) { + global $conn, $contractNoticeFields, $contractNoticeInsertQ; $row = 1; $handle = fopen($fpath, "r"); //"t" mode string translates windows line breaks to unix - $datamapping0711 = array( + $datamapping0712 = array( "Agency" => "agencyName", "Parent CN ID" => "parentCN", "CN ID" => "CNID", @@ -86,6 +61,7 @@ "Value" => "value", "Description" => "description", "Agency Ref Id" => "agencyID", + "Agency Ref. ID" => "agencyID", "Category" => "category", "Procurement Method" => "procurementMethod", "ATM ID" => "atmID", @@ -109,6 +85,7 @@ "Agency Postcode" => "contactPostcode", "" => "" ); + $headers; while (($data = fgetcsv($handle, 1000, "\t")) !== false) { @@ -116,96 +93,57 @@ if ($row == 3) { $headers = $data; } elseif ($row > 3) { - if ($num > count($datamapping0711)) { + 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($datamapping0711); + $keys = array_keys($datamapping0712); 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 && $data[$c] != '0') { - $contractNoticeInsert[] = $data[$c]; - } else { - $contractNoticeInsert[] = null; - } - } elseif (($datamapping0711[$headers[$c]]) == "supplierABN") { - if ($data[$c] > 0 && $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])); + 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 { - if (strstr("\" =", $data[$c] > 0)) { - die("Invalid Description field" . $contractNoticeInsert); - } - $colvalue = preg_replace('/[^[:print:]]/', '', utf8_encode($data[$c])); + $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; - } - } - } 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]; + $contractNoticeInsert[] = $colvalue; } } } 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"; + $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>"; } - } 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"; - } + echo $data[2] . " failed CN insert.<br>" . print_r($errors, true) . "<br> row $row <br><br>\n"; } + flush(); //echo "<hr>\n"; } @@ -213,9 +151,7 @@ } fclose($handle); $contractNoticeInsertQ->closeCursor(); - $supplierInsertQ->closeCursor(); - $agencyInsertQ->closeCursor(); - + return $success; } @@ -229,14 +165,13 @@ // loop through all of the files while (false !== ($fname = readdir($dhandle))) { if (($fname != '.') && ($fname != '..')) { - $files[date("c", filemtime($path . $fname)).md5($fname)] = $fname; - } + $files[date("c", filemtime($path . $fname)) . md5($fname)] = $fname; + } } } ksort($files); foreach ($files as $date => $fname) { - echo "<a href=\"import.php?fname=$fname\">$fname</a> " . filesize($path . $fname) . " " . $date . "<br/>"; - + echo "<a href=\"import.php?fname=$fname\">$fname</a> " . filesize($path . $fname) . " " . $date . "<br/>"; } } else { $success = 0; @@ -247,9 +182,9 @@ $success+= processFile($path . $fname, "agency"); $success+= processFile($path . $fname, "supplier"); echo "<br> $success records successfully created"; - + flush(); - // run post import data processing + // run post import data processing // cn echo "link amend<br>"; include ("linkAmendments.php");