";
echo "Found $entries entries\n
";
echo "Skipped $duplicates existing entries
";
echo "Inserted $inserts new records
";
} 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\"
";
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\"
";
return false;
}
}
?>