FreenetIS

$cond_number = '';

$cond_vs = 'IS NULL';

$cond_vs = '=' . $this->db->escape_str($data->variable_symbol);

$cond_number = 'AND bt.number=' . $this->db->escape($data->number);

require_once APPPATH."libraries/importers/Unicredit/UnicreditImport.php";

require_once APPPATH."libraries/importers/Unicredit/UnicreditSaver.php";

const CSV_UNICREDIT = 3;

self::$types[self::CSV_FIO] = 'Fio CSV';

self::$types[self::CSV_UNICREDIT] = 'Unicredit CSV';

case self::CSV_UNICREDIT:

$this->import_unicredit(

$id, $form->listing->value,

@$form_data['send_email_notice'] == 1,

@$form_data['send_sms_notice'] == 1

);

break;

* @param integer $back_account_id

* @param boolean $send_emails Send emails as payment accept notification?

* @param boolean $send_sms Send SMSs as payment accept notification?

private function import_fio($bank_account_id, $file_url, $send_emails, $send_sms)

* @param integer $back_account_id

* @param string $file_url

* @param boolean $send_emails Send emails as payment accept notification?

* @param boolean $send_sms Send SMSs as payment accept notification?

private function import_ebank($bank_account_id, $url, $send_emails, $send_sms)

/**

* Imports fio bank listing items from specified file.

*

* @author Ondrej Fibich

* @param integer $back_account_id

* @param string $file_url

* @param boolean $send_emails Send emails as payment accept notification?

* @param boolean $send_sms Send SMSs as payment accept notification?

*/

private function import_unicredit($bank_account_id, $file_url, $send_emails, $send_sms)

{

try

{

$db = new Transfer_Model();

$db->transaction_start();

// parse bank listing items

$data = UnicreditImport::getDataFromFile($file_url);

// get header

$header = UnicreditImport::getListingHeader();

// does match bank account in system with bank account of statement?

$ba = new Bank_account_Model($bank_account_id);

if ($ba->account_nr != $header['account_nr'] &&

$ba->bank_nr != $header['bank_nr'])

{

$ba_nr = $ba->account_nr.'/'.$ba->bank_nr;

$listing_ba_nr = $header['account_nr'].'/'.$header['bank_nr'];

throw new UnicreditException(__(

'Bank account number in listing (%s) header does not match ' .

'bank account %s in database!', array($listing_ba_nr, $ba_nr)

));

}

// save bank statement

$statement = new Bank_statement_Model();

$statement->set_logger(FALSE);

$statement->bank_account_id = $bank_account_id;

$statement->user_id = $this->user_id;

$statement->type = self::$types[self::CSV_UNICREDIT];

$statement->from = $header['from'];

$statement->to = $header['to'];

$statement->save_throwable();

// save bank listing items

$stats = UnicreditSaver::save(

$data, $bank_account_id, $statement->id,

$this->user_id, $send_emails, $send_sms

);

$db->transaction_commit();

}

catch (UnicreditException $e)

{

$db->transaction_rollback();

status::error(__('Import has failed.') . ' ' . $e->getMessage(), FALSE);

url::redirect('bank_accounts/show_all');

}

catch (Duplicity_Exception $e)

{

$db->transaction_rollback();

status::error(

__('Import has failed.') . ' ' .

__('Bank statement contains items that were already imported.') . ' ' .

$e->getMessage(), FALSE

);

url::redirect('bank_accounts/show_all');

}

catch (Exception $e)

{

$db->transaction_rollback();

Log::add_exception($e);

status::error(

__('Import has failed') . '.<br>' . $e->getMessage(), FALSE

);

url::redirect('bank_accounts/show_all');

}

url::redirect('bank_transfers/show_by_bank_statement/'.$statement->id);

}

<?php defined('SYSPATH') or die('No direct script access.');

/*

* This file is part of open source system FreenetIS

* and it is released under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*

*/

require_once 'UnicreditParser.php';

/**

* Main class for parsing bank account listings from czech bank "Unicredit banka".

* The listings are downloaded from the ebanking web application.

*

* Additional changes for Freenetis (Jiri Svitak 2011-08-18):

* added file import support

* other improvements that enable cooperation with Freenetis

*

* @abstract Class for parsing bank account listings from czech bank "Unicredit banka".

* @author Petr Hruska, Lukas Turek, Jiri Svitak, David Kuba, Ondrej Fibich

* @copyright 2009-2011 Petr Hruska, Lukas Turek, o.s. CZF-Praha, Jiri Svitak, o.s. Unart

* @link http://www.praha12.net

*/

class UnicreditImport

{

/**

* Gets data from manully imported csv file.

*

* @throws UnicreditException on error

* @author Jiri Svitak

* @param string $file

*/

public static function getDataFromFile($file)

{

if (($csvData = file_get_contents($file)) === FALSE)

{

throw new UnicreditException(__('Cannot open uploaded bank listing file!'));

}

$csvData = iconv('cp1250', 'UTF-8', $csvData);

$data = UnicreditParser::parseCSV($csvData);

// clean up from needless attributes is not necessary due to changing Fio csv format

self::correctData($data);

return $data;

}

/**

* Returns bank listing header information, information is provided only after parsing.

*

* @return array

*/

public static function getListingHeader()

{

return UnicreditParser::getListingHeader();

}

/**

* Corrects data.

*

* @throws UnicreditException on error

* @param array $data

*/

public static function correctData(&$data)

{

foreach ($data as &$row)

{

if ($row['mena'] != 'CZK')

{

throw new UnicreditException(__('Unknown currency %d.'), $row['mena']);

}

$row['nazev_banky'] .= $row['nazev_banky_2'];

$row['adresa'] .= $row['adresa_2'];

$row['adresa'] .= $row['adresa_3'];

$row['zprava'] .= $row['zprava_2'];

$row['zprava'] .= $row['zprava_3'];

$row['zprava'] .= $row['zprava_4'];

$row['zprava'] .= $row['zprava_5'];

$row['zprava'] .= $row['zprava_6'];

// convert from cents

$row['castka'] /= 100;

}

}

}

<?php defined('SYSPATH') or die('No direct script access.');

/*

* This file is part of open source system FreenetIS

* and it is released under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*

*/

require_once "UnicreditException.php";

/**

* Auxiliary class for parsing CSV bank account listings from czech bank "Unicredit banka".

* The CSV listings are downloaded from the ebanking and stored at dafined path.

* The CSV format looks like this:

Název účtu;Číslo účtu;Měna;Zůstatek

HLAVNÍ BÚ V BALÍČKU (PO);1002392380;CZK;0,000

Účet;Částka;Měna;Datum zaúčtování;Valuta;Banka;Název banky;Název banky;Číslo účtu;Název účtu;Adresa;Adresa;Adresa;Detaily transakce;Detaily transakce;Detaily transakce;Detaily transakce;Detaily transakce;Detaily transakce;Konstatní kód;Variabilní kód;Specifický kód;Platební titul;Reference

1002392380;600,000;CZK;2012-02-24;2012-02-24;;;;;;;;;VKLAD HOTOVOSTI V CZK;;;;;;0558;470500;;;

** {etc.}

* @abstract Class for parsing bank account listings from czech bank "Unicredit banka".

* @author Petr Hruska, Lukas Turek, Tomas Dulik, Jiri Svitak, Ondrej Fibich

* @copyright 2009-2011 Petr Hruska, Lukas Turek, o.s. CZF-Praha, Tomas Dulik, Jiri Svitak, o.s. UnArt

* @link http://www.praha12.net

*/

class UnicreditParser

{

/**

* Account number

*

* @var integer

*/

private static $account_nr = 0;

/**

* Bank number

*

* @var integer

*/

private static $bank_nr = 2700;

/**

* Date from

*

* @var string

*/

private static $from = NULL;

/**

* Date to

*

* @var string

*/

private static $to = NULL;

/**

* All fields available must be used and sorted alphabetically

*

* @var array[string]

*/

private static $fields = array

(

'ucet' => 'Účet',

'castka' => 'Částka',

'mena' => 'Měna',

'datum' => 'Datum zaúčtování',

'datum_valuta' => 'Valuta',

'kod_banky' => 'Banka',

'nazev_banky' => 'Název banky',

'nazev_banky_2' => 'Název banky',

'protiucet' => 'Číslo účtu',

'nazev_protiuctu' => 'Název účtu',

'adresa' => 'Adresa',

'adresa_2' => 'Adresa',

'adresa_3' => 'Adresa',

'zprava' => 'Detaily transakce',

'zprava_2' => 'Detaily transakce',

'zprava_3' => 'Detaily transakce',

'zprava_4' => 'Detaily transakce',

'zprava_5' => 'Detaily transakce',

'zprava_6' => 'Detaily transakce',

'ks' => 'Konstatní kód',

'vs' => 'Variabilní kód',

'ss' => 'Specifický kód',

'platebni_titul' => 'Platební titul',

'reference' => 'Reference',

);

/**

* Returns associative array containing important listing header information.

* Must be called after parsing.

*

* @author Jiri Svitak

* @return header information

*/

public static function getListingHeader()

{

// account number

$header['account_nr'] = self::$account_nr;

$header['bank_nr'] = self::$bank_nr;

$header['from'] = self::$from;

$header['to'] = self::$to;

return $header;

}

/**

* The core of the parsing is done by this function.

*

* @param string $csv string containing the original csv file.

* @return array[array] Integer-indexed array of associative arrays.

* Each associative array represents one line of the CSV

* @throws UnicreditException

*/

public static function parseCSV($csv)

{

$sum = 0;

$data = array();

$keys = array_keys(self::$fields);

$number = 0;

foreach (explode("\n", $csv) as $line)

{

$line = trim($line);

// next line if line is empty

if (empty($line))

{

continue;

}

// second line of file - account name, account number, currency, account balance

if ($number == 1)

{

$line_arr = explode(';', $line);

if (count($line_arr) < 4)

{

throw new UnicreditException('Nesprávný formát na prvním řádku.');

}

self::$account_nr = $line_arr[1];

}

// 3rd line, checking column header names and count

else if ($number == 2)

{

self::checkHeaders($line);

}

// data lines

else if ($number >= 3)

{

// split each line into assoc. array

$cols = self::parseLine($line, $keys);

$sum += $cols['castka'];

if (empty(self::$from))

{

self::$from = self::$to = $cols['datum'];

}

else

{

self::$to = $cols['datum'];

}

$data[] = $cols;

}

// next line

$number++;

}

if ($number <= 1)

{

throw new UnicreditException('CSV soubor není kompletní.');

}

return $data;

}

/**

* Checks headers

*

* @param integer $line

* @throws UnicreditException

*/

private static function checkHeaders($line)

{

$expected = implode(';', self::$fields);

if ($line != $expected)

{

throw new UnicreditException(

"Nelze parsovat hlavičku Unicredit výpisu. " .

"Ujistěte se, že jste zvolili správný formát " .

"souboru k importu v internetovém bankovnictví." .

"\n$line\n$expected"

);

}

}

/**

* Normalize string amount to double value

*

* @param string $amount

* @return double

* @throws UnicreditException

*/

private static function normalizeAmount($amount)

{

$amount = str_replace(array(' ', ','), array('', ''), $amount);

if (!is_numeric($amount))

{

throw new UnicreditException('Chybný formát částky převodu.');

}

return doubleval($amount/10);

}

/**

* Parse line of dump

*

* @param string $line

* @param array $keys

* @return array

* @throws UnicreditException

*/

private static function parseLine($line, $keys)

{

$cols = explode(';', $line);

if (count($cols) != count(self::$fields))

{

throw new UnicreditException(__(

'Wrong count of fields (expected %d, get %d in line: %s)',

array(count(self::$fields), count($cols), $line)

));

}

// Convert to associative array

$cols = array_combine($keys, $cols);

// Amount has to be converted

$cols['castka'] = self::normalizeAmount($cols['castka']);

return $cols;

}

}

<?php

/*

* This file is part of open source system FreeNetIS

* and it is released under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*

*/

/**

* Unicredit importer exception

* @author Jiri Svitak, David Kuba

*/

class UnicreditException extends Exception {}

<?php defined('SYSPATH') or die('No direct script access.');

/*

* This file is part of open source system FreenetIS

* and it is released under GPLv3 licence.

*

* More info about licence can be found:

* http://www.gnu.org/licenses/gpl-3.0.html

*

* More info about project can be found:

* http://www.freenetis.org/

*

*/

/**

* Saves Unicredit bank listing items into Freenetis database structure.

*

* @author David Kuba, Ondrej Fibich

*/

class UnicreditSaver

{

/**

* Saves Unicredit bank listing items into Freenetis database structure.

*

* @param object $data

* @param integer $bank_account_id

* @param integer $bank_statement_id

* @param integer $user_id

* @param boolean $send_emails

* @param boolean $send_sms

* @return array Stats

*/

public static function save(

$data, $bank_account_id, $bank_statement_id, $user_id,

$send_emails, $send_sms)

{

// preparation of system double-entry accounts

$suppliers = ORM::factory('account')

->where('account_attribute_id', Account_attribute_Model::SUPPLIERS)

->find();

$member_fees = ORM::factory('account')

->where('account_attribute_id', Account_attribute_Model::MEMBER_FEES)

->find();

$operating = ORM::factory('account')

->where('account_attribute_id', Account_attribute_Model::OPERATING)

->find();

$cash = ORM::factory('account')

->where('account_attribute_id', Account_attribute_Model::CASH)

->find();

$ba = new Bank_account_Model($bank_account_id);

$account = $ba->get_related_account_by_attribute_id(Account_attribute_Model::BANK);

$bank_interests = $ba->get_related_account_by_attribute_id(Account_attribute_Model::BANK_INTERESTS);

// model preparation

$bt = new Bank_transfer_Model();

$member_model = new Member_Model();

$fee_model = new Fee_Model();

$vs_model = new Variable_Symbol_Model();

$counter_ba = new Bank_account_Model();

// statistics preparation

$stats['unidentified_nr'] = 0;

$stats['invoices'] = 0;

$stats['invoices_nr'] = 0;

$stats['member_fees'] = 0;

$stats['member_fees_nr'] = 0;

$stats['interests'] = 0;

$stats['interests_nr'] = 0;

$stats['deposits'] = 0;

$stats['deposits_nr'] = 0;

// miscellaneous preparation

$now = date('Y-m-d H:i:s');

$number = 0;

// saving each bank listing item

foreach ($data as $item)

{

// convert date of transfer to international format

$datetime = $item['datum'];

// check

if ($item['castka'] < 0)

{

throw new UnicreditException(__('Outbound transfers not supported'));

}

// check bank account

if ($item['ucet'] != $ba->account_nr)

{

throw new UnicreditException(__(

'Account number not match %s != %s',

array($ba->account_nr, $item['ucet'])

));

}

// check for duplicities

$dupli_object = new stdClass();

$dupli_object->variable_symbol = $item['vs'];

$dupli_object->number = $number;

$dupli_object->date_time = $datetime;

$dupli_object->comment = $item['zprava'];

if ($bt->get_duplicities($dupli_object)->count() > 0)

{

throw new Duplicity_Exception();

}

/* Step one - find or create bank account */

$counter_ba_id = NULL;

$counter_ba_id_added = FALSE;

if (mb_strpos($item['zprava'], 'ZÁLOHA') === FALSE &&

mb_strpos($item['zprava'], 'VKLAD HOTOVOSTI') === FALSE)

{

// try to find counter bank account in database

$counter_ba->where(array

(

'account_nr' => $item['protiucet'],

'bank_nr' => $item['kod_banky']

))->find();

// counter bank account does not exist? let's create new one

if (!$counter_ba->id)

{

$counter_ba->clear();

$counter_ba->set_logger(FALSE);

$counter_ba->name = $item['nazev_protiuctu'];

$counter_ba->account_nr = $item['protiucet'];

$counter_ba->bank_nr = $item['kod_banky'];

$counter_ba->member_id = NULL;

$counter_ba->save_throwable();

$counter_ba_id_added = TRUE;

}

$counter_ba_id = $counter_ba->id;

}

/* Step two - get account */

$m_account_id = NULL;

$member_id = NULL;

$m_account = $vs_model->where('variable_symbol', $item['vs'])->find()->account;

if ($m_account->id && $m_account->member_id)

{

$m_account_id = $m_account->id;

$member_id = $m_account->member_id;

}

/* Step three - create transfers to association account */

// double-entry incoming transfer

$transfer_id = Transfer_Model::insert_transfer(

$member_fees->id, $account->id, null, $member_id,

$user_id, null, $datetime, $now, $item['zprava'],

abs($item['castka'])

);

// incoming bank transfer

$bt->clear();

$bt->set_logger(false);

$bt->origin_id = $counter_ba_id;

$bt->destination_id = $ba->id;

$bt->transfer_id = $transfer_id;

$bt->bank_statement_id = $bank_statement_id;

$bt->number = $number;

$bt->constant_symbol = $item['ks'];

$bt->variable_symbol = $item['vs'];

$bt->specific_symbol = $item['ss'];

$bt->save_throwable();

/** Step four - create transfers to associated member (if found) */

if (!empty($member_id))

{

$a_transfer_id = Transfer_Model::insert_transfer(

$account->id, $m_account_id, $transfer_id, $member_id,

$user_id, null, $datetime, $now,

__('Assigning of transfer'), abs($item['castka'])

);

// transaction fee

$fee = $fee_model->get_by_date_type($datetime, 'transfer fee');

if ($fee && $fee->fee > 0)

{

$tf_transfer_id = Transfer_Model::insert_transfer(

$m_account_id, $operating->id, $transfer_id,

$member_id, $user_id, null, $datetime,

$now, __('Transfer fee'), $fee->fee

);

}

if ($counter_ba_id_added)

{

$counter_ba->member_id = $member_id;

$counter_ba->save_throwable();

}

}

else

{

$stats['unidentified_nr']++;

}

// member fee stats

$stats['member_fees'] += abs($item['castka']);

$stats['member_fees_nr']++;

/** Send payment notification */

try

{

Message_Model::activate_special_notice(

Message_Model::RECEIVED_PAYMENT_NOTICE_MESSAGE,

$member_id, $user_id,

$send_emails, $send_sms

);

}

catch (Exception $e)

{

Log::add_exception($e);

}

// line number increase

$number++;

}

return $stats;

}

}