expenses/request/import.php

<?
	include_once("../php/include-request.php");
	include_once("../php/spreadsheet-reader/SpreadsheetReader.php");

	verifyAuthorized();

	$action = getMandatoryRequestValue("action");

	if($action == "import") {
		$bank = getMandatoryRequestValue("bank");
		$accountId = getMandatoryRequestValue("accountId");

		if(!isset($_FILES['dataFile'])) {
	        requestFail("You must specify a file", true);
	    }

		$dataFile = $_FILES['dataFile']['tmp_name'];
		$originalFilename = $_FILES['dataFile']['name'];

		$duplicates = 0;
		$inserts = 0;
		$entries = 0;

		initDatabaseConnection();

		$maxId = dbQuery("SELECT max(id) AS maxId FROM record")[0]['maxId'];
		if($maxId == "") {
			$maxId = -1;
		}

		if($bank == "seb") {
			requestOk("");
			parseBankSeb($dataFile, $originalFilename);
		} else if($bank == "handelsbanken") {
			requestOk("");
			parseBankHandelsbanken($dataFile);
		} else {
			requestFail("Unknown bank \"$bank\"", true);
		}
		closeDatabaseConnection();

		echo "<br/>";
		echo "Found <b>$entries</b> entries\n<br/>";
		echo "Skipped <b>$duplicates</b> existing entries<br/>";
		echo "Inserted <b>$inserts</b> new records<br/>";
	} else {
		requestFail("Unknown request action \"$action\"", true);
	}

	function parseBankSeb($excelFile, $originalFilename) {
		$reader = new SpreadsheetReader($excelFile, $originalFilename);
		$parse = false;

		foreach($reader as $row) {
			if(!$parse) {
				if(count($row) > 0 && $row[0] == 'Bokföringsdatum') {
					$parse = true;
				}
			} else {
				list($year, $month, $day) = explode("-", $row[0]);
				$transactionId = $row[1]."-".$row[2];
				$label = $row[3];
				$amount = $row[4];

				$ok = insertRecord($year, $month, $day, $transactionId, $label, $amount);
				if(!$ok) {
					break;
				}
			}
			//print_r($row);
		}
	}

	function parseBankHandelsbanken($fakeExcelFile) {
		global $entries;
		$html = file_get_contents($fakeExcelFile);

		$dom = new DOMDocument();
		$dom->loadHtml($html);

		$tables = $dom->getElementsByTagName("table");
		$dataTable = $tables->item(3); // 4th table holds the actual data
		$skipFirst = true;
		foreach($dataTable->childNodes as $tr) {
			if($skipFirst) {
				$skipFirst = false;
			} else {
				$tds = $tr->childNodes;
				$regDate   = $tds->item(0)->nodeValue;
				$transDate = $tds->item(2)->nodeValue;
				$label     = $tds->item(4)->nodeValue;
				$amount    = $tds->item(6)->nodeValue;

				list($year, $month, $day) = explode("-", $regDate);
				$transactionId = $transDate;
				$amount = str_replace(" ", "", $amount); // remove "tusen-avdelare"
				$amount = str_replace(",", ".", $amount); // use dot for decimal

				$ok = insertRecord($year, $month, $day, $transactionId, $label, $amount);
				if(!$ok) {
					break;
				}
			}
		}
	}

	function insertRecord($year, $month, $day, $transactionId, $label, $amount) {
		global $db, $duplicates, $inserts, $accountId, $maxId, $entries;

		$transactionId = trim($transactionId);
		$label = trim($label);

		$entries++;
		$expense = ($amount < 0 ? 1 : 0);
		$amount = abs($amount);

		$rows = dbQuery(
				"SELECT id
				 FROM record
				 WHERE year = ? AND month = ? AND day = ?
				 AND account_id = ?
				 AND transaction_id = ?
				 AND label = ?
				 AND amount = ?
				 AND expense = ?
				 AND id <= ?
				",
				$year, $month, $day,
				$accountId,
				$transactionId,
				$label,
				$amount,
				$expense,
				$maxId
		);

		if(count($rows) > 0) {
			$duplicates++;
			//echo "Duplicate entry: $year-$month-$day; $amount ".getHouseholdCurrency()."; \"$transactionId\"; \"$label\"<br/>";
			return true;
		} else {
			$rowCount = dbUpdate("
					INSERT INTO record(
						account_id,
						year,
						month,
						day,
						transaction_id,
						label,
						amount,
						expense,
						category_id
					) VALUES (
						?,
						?,
						?,
						?,
						?,
						?,
						?,
						?,
						(SELECT c.id
						 FROM category c,
						      category_group g
						 WHERE c.category_group_id = g.id
						 AND g.household_id = ?
						 AND g.expense = ?
						 AND g.system = 1
						)
					)",
					$accountId,
					$year,
					$month,
					$day,
					$transactionId,
					$label,
					$amount,
					$expense,
					getHouseholdId(),
					$expense
			);
			if($rowCount) {
				$inserts += $rowCount;
				return true;
			}
			echo "Failed inserting record: $year-$month-$day; $amount ".getHouseholdCurrency()."; \"$transactionId\"; \"$label\"<br/>";
			return false;
		}
	}
?>