neil.mckillop.org

• https://www.sqlite.org/download.html
• https://sqlitebrowser.org/dl/

CREATE TABLE `jobs` (
	`job_reference`	TEXT,
	`job_title`	TEXT,
	`job_status`	TEXT,
	`reply_to`	TEXT
);
 
 
CREATE TABLE `job_history` (
	`job_reference`	TEXT,
	`field_name`	TEXT,
	`old_value`	TEXT,
	`new_value`	TEXT,
	`changed_on`	TEXT
);

extract.php

<?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();
	}
}
 
 
 
?>