neil.mckillop.org

This is an old revision of the document!

---

<?php

$filename = “grant.csv”; $content = file_get_contents($filename); $lines = explode(“\n”, $content); unset($lines[0]);

$field_name[0] = “reply_to”; $field_name[1] = “job_status”; $field_name[2] = “job_reference”; $field_name[3] = “job_title”;

foreach($lines as $line) {

if(empty($line))
	continue;
$fields = explode("\t", $line);
$job_reference = $fields[2];
foreach($field_name as $field_pos=>$db_field_name) {
	$data[$job_reference][$db_field_name] = $fields[$field_pos];
}

}

$myDB = new PDO('sqlite:/home/seven/tmp/grant.db');

foreach($data as $job_ref⇒$val) {

if(empty($val['job_reference']))
	continue;
$job_reference = $val['job_reference'];
$sql = "select * from jobs where job_reference= :job_reference";
$stmt = $myDB->prepare($sql);
  	$stmt->bindParam(':job_reference', $job_reference, PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetch();
if($results) {
	foreach($field_name as $field_pos=>$db_field_name) {
		if($val[$db_field_name] != $results[$db_field_name]) {
			$changed_on = date("Y-m-d H:i");
			echo "Field $db_field_name changed from {$results[$db_field_name]} to {$val[$db_field_name]}\n";
			$sql = "insert into job_history(job_reference, field_name, old_value, new_value, changed_on) values(:job_reference, :field_name, :old_value, :new_value, :changed_on)";
			$stmt_change = $myDB->prepare($sql);
			$stmt_change->bindParam(':job_reference', $job_reference, PDO::PARAM_STR);
			$stmt_change->bindParam(':field_name', $db_field_name, PDO::PARAM_STR);
			$stmt_change->bindParam(':new_value', $val[$db_field_name], PDO::PARAM_STR);
			$stmt_change->bindParam(':old_value', $results[$db_field_name], PDO::PARAM_STR);
			$stmt_change->bindParam(':changed_on', $changed_on, PDO::PARAM_STR);
			$stmt_change->execute();
			$sql = "update jobs set $db_field_name = :new_value where job_reference = :job_reference";
			$stmt_update = $myDB->prepare($sql);
			$stmt_update->bindValue(':job_reference', $job_reference, PDO::PARAM_STR);
			$stmt_update->bindValue(':new_value', $val[$db_field_name], PDO::PARAM_STR);
			$stmt_update->execute();
		}
	}
} else {
		echo "Insert row\n";
$field_names = implode(",", array_values($field_name));
foreach($field_name as $pos_id=>$field_name_val) {
	$val_names[] = ":".$field_name_val;
}
$value_names = implode(",", $val_names);
$sql = "insert into jobs($field_names) values($value_names)";
$stmt = $myDB->prepare($sql);

foreach($field_name as $field_pos=>$db_field_name) {
	$bind_name = ":".$db_field_name;
	$stmt->bindParam($bind_name, $val[$db_field_name], PDO::PARAM_STR);
}
$stmt->execute();
}

}

?>