Easybank Kontoauszug analysieren

Der Kontoauszug kann als CSV Datei heruntergeladen werden und liegt als /tmp/umsaetze.csv vor. Dieser Kontoauszug wird nach regulären Ausdrücken durchsucht und für jeden Ausdruck ein eigenes Tabellenblatt in einer Datei /tmp/konten.xlsx angelegt. Achtung auf die Reihenfolge der Suchbegriffe, da einfache Suchbegriffe sonst zu viele Kontozeilen filtern könnten.

 

<?php

 

/* Easybank Kontoauszug verwurschten

 * Erzeugt aus der Kontoauszug-Datei uemsaetze.csv eine Excel-Datei

 * in der die einzelnen Blättere einem Suchbegriff entsprechen (z.B: KEST )

 * kner 2017

 * Datastructure:

 Array

(

    [KEST] => Array

        (

            [2018] => Array

                (

                    [1] => Array

                        (

                            [date] => 02.01.2018

                            [value] => -0.01

                            [info] => Einbehaltene KESt                            BG/000001039

                        )

 

                    [sum] => -0.01

                )

 

            [2017] => Array

                (

                    [73] => Array

                        (

                            [date] => 02.10.2017

                            [value] => -0.02

                            [info] => Einbehaltene KESt                            BG/000000967

                        )

 

                    [sum] => -0.07

                    [145] => Array

                        (

                            [date] => 03.07.2017

                            [value] => -0.01

                            [info] => Einbehaltene KESt                            BG/000000895

                        )

 

                    [237] => Array

 

 *

 *

 *

 */

$searcharray = array(

    'Zinsen' => "#.*Zinsen.*#i",

    'KEST' => "#.*kest.*#i",

     'Wasser u. Müll' => "#(.*gva.*)|(.*wlv.*)|(.*Abfallwirtsch.*)#i",

     'GdeTraisk' => "#(.*Stadtgemeinde.*)#i",

);

 

 

 

 

$row = 1;

$inputdata = array();

$result = array();

$remember_found_indices = array();

$filename = file_exists("/tmp/umsaetze.csv")? "/tmp/umsaetze.csv":"umsaetze.csv";

if (($handle = fopen("/tmp/umsaetze.csv", "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

        $num = count($data);

        //      echo "<p> $num fields in line $row: <br /></p>\n";

        $inputdata[$row]['date'] = $data[2];

        $inputdata[$row]['value'] = floatval(str_replace(',', '.', str_replace('.', '', $data[4])));

        ;

        $inputdata[$row]['info'] = utf8_encode($data[1]);

 

        $row++;

    }

    fclose($handle);

}

// search for regular exp and book to separat accounts

 

foreach ($inputdata as $key => $value) {

    $info = $value['info'];

    foreach ($searcharray as $k => $toSearch) {

        preg_match($toSearch, $info, $foundit);

        if ($foundit) {

            $dateValue = strtotime($value['date']);

            $year = date("Y", $dateValue);

 

            $result[$k][$year][$key] = $value;

            if (!isset($result[$k][$year]["sum"]))

                $result[$k][$year]["sum"] = floatval(0.0);

            $x = floatval($value['value']);

            $result[$k][$year]["sum"] += $x;

            $remember_found_indices[] = $key;

            break;

        }

    }

}

// delete all found items to get the rest of the bookings

 

foreach ($remember_found_indices as $k => $val) {

    unset($inputdata[$val]);

}

 

// store all bookings not found yet

 

foreach ($inputdata as $key => $value) {

    $dateValue = strtotime($value['date']);

    $year = date("Y", $dateValue);

 

    $result["rest"][$year][$key] = $value;

    if (!isset($result["rest"][$year]["sum"])) {

        $result["rest"][$year]["sum"] = floatval(0.0);

    }

    $x = floatval($value['value']);

    $result["rest"][$year]["sum"] += $x;

}

 

// just for debugging

print_r($result);

 

/*

 * Installation of the libraries by using composer:

 * 1. sudo apt-get install composer

 * 2. composer im Projektverzeichnis aufrufen:

 *    composer require phpoffice/phpspreadsheet

 *

 */

 

require 'vendor/autoload.php';

 

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

 

$spreadsheet = new Spreadsheet();

 

// textWrap funzt nicht in Libreoffice Calc

//$spreadsheet->getDefaultStyle()->getAlignment()->setWrapText(true);

 

 

//create the excel sheet

foreach ($result as $key => $years) {

    $sheet[$key] = $spreadsheet->createSheet();

    $sheet[$key]->setTitle($key);

    //$sheet[$key]->getStyle('A1:Z90')->getAlignment()->setWrapText(true);

    //$sheet[$key]->getDefaultRowDimension()->setRowHeight(-1);

    //$sheet[$key]->getStyle('A1:Y99')->getAlignment()->setWrapText(true);

 

    $col = 1;

    $sum = 0;

    foreach ($years as $year => $bookings) {

        $sheet[$key]->setCellValueByColumnAndRow($col, 1, $year);

        $row = 2;

        foreach ($bookings as $nr => $booking) {

            if ($nr == "sum") {

                $sum = $booking;

                continue;

            }

            $sheet[$key]->setCellValueByColumnAndRow($col, $row, $nr);

            $sheet[$key]->setCellValueByColumnAndRow($col + 1, $row, $booking['date']);

            $sheet[$key]->setCellValueByColumnAndRow($col + 2, $row, $booking['value']);

            $sheet[$key]->setCellValueByColumnAndRow($col + 3, $row, $booking['info']);

            $row++;

        }

        $sheet[$key]->setCellValueByColumnAndRow($col + 2, $row, $sum);

        $col += 5;

    }

}

 

$spreadsheet->removeSheetByIndex(0);

$writer = new Xlsx($spreadsheet);

$writer->save('/tmp/konten.xlsx');