Revize 1227
Přidáno uživatelem Jiří Sviták před asi 13 roky(ů)
freenetis/branches/testing/application/i18n/cs_CZ/texts.php | ||
---|---|---|
'balance from date' => 'Zůstatek od data',
|
||
'balance to date' => 'Zůstatek do data',
|
||
'bank account' => 'Bankovní účet',
|
||
'bank account number in listing (%s) header does not match bank account %s in database!' => 'Číslo bankovního účtu v hlavičce výpisu (%s) neodpovídá bankovnímu účtu (%s) v databázi!',
|
||
'bank account id' => 'ID bankovního účtu',
|
||
'bank account name' => 'Název bankovního účtu',
|
||
'bank accounts' => 'Bankovní účty',
|
||
... | ... | |
'bank interest' => 'Bankovní úrok',
|
||
'bank interests' => 'Bankovní úroky',
|
||
'bank statement contains items that were already imported' => 'Bankovní výpis obsahuje položky, které již byly importovány.',
|
||
'bank statement has been successfully deleted' => 'Bankovní výpis byl úspěšně smazán.',
|
||
'bank statement (including %d transfers and %d bank transfers) has been successfully deleted' => 'Bankovní výpis (včetně %d převodů a %d bankovních převodů) byl úspěšně smazán.',
|
||
'bank statement has been successfully updated' => 'Bankovní výpis byl úspěšně upraven.',
|
||
'bank statements' => 'Bankovní výpisy',
|
||
'bank templates' => 'Bankovní šablony',
|
||
... | ... | |
'cannot find detail dumps' => 'Nemohu nalézt podrobné výpisy.',
|
||
'cannot load heading of invoice' => 'Nemohu načíst hlavičku faktury',
|
||
'cannot load services - data missing' => 'Nemohu načíst služby - data chybí',
|
||
'cannot open uploaded bank listing file!' => 'Nelze otevřít nahraný soubor s bankovním výpisem!',
|
||
'cannot parse fio listing header!' => 'Nelze parsovat hlavičku Fio výpisu!',
|
||
'cannot save form' => 'Nemohu uložit formulář',
|
||
'cannot save gps, this address point has already gps coordinates' => 'Nemohu uložit GPS, adresní bod má již přiřazeny GPS souřadnice',
|
||
'cash' => 'Pokladna',
|
freenetis/branches/testing/application/models/transfer.php | ||
---|---|---|
* @param integer $id
|
||
* @throws Kohana_Databse_Exception
|
||
*/
|
||
public static function delete_transfer($id)
|
||
public static function delete_transfer($id = null)
|
||
{
|
||
if (!isset($id))
|
||
throw new Kohana_User_Exception ("delete transfer", "ID to delete has not been supplied!");
|
||
$transfer = new Transfer_Model($id);
|
||
if (!$transfer->id)
|
||
throw new Kohana_User_Exception ("delete transfer", "Transfer to delete has not been found!");
|
||
// update balance of origin account
|
||
$oa = new Account_Model($transfer->origin_id);
|
||
$oa->balance += $transfer->amount;
|
freenetis/branches/testing/application/controllers/bank_transfers.php | ||
---|---|---|
__('Show statements')
|
||
);
|
||
}
|
||
if ($this->acl_check_new('Accounts_Controller', 'bank_transfers'))
|
||
{
|
||
$grid->add_new_button(
|
||
url_lang::base().'import/upload_bank_file/'.$bank_account_id,
|
||
__('Upload bank transfers listing')
|
||
);
|
||
|
||
}
|
||
}
|
||
|
||
$grid->order_field('datetime')
|
freenetis/branches/testing/application/controllers/bank_statements.php | ||
---|---|---|
|
||
$grid->order_field('closing_balance')
|
||
->label(__('Closing balance'));
|
||
|
||
|
||
if ($this->acl_check_new('Accounts_Controller', 'bank_transfers'))
|
||
{
|
||
$grid->add_new_button(
|
||
url_lang::base().'import/upload_bank_file/'.$bank_account_id,
|
||
__('Upload bank transfers listing')
|
||
);
|
||
}
|
||
|
||
$actions = $grid->grouped_action_field();
|
||
|
||
if ($this->acl_check_view('Accounts_Controller', 'bank_transfers'))
|
||
... | ... | |
->icon_action('edit')
|
||
->url('bank_statements/edit');
|
||
}
|
||
|
||
if ($this->acl_check_delete('Accounts_Controller', 'bank_statements'))
|
||
{
|
||
$actions->add_action('id')
|
||
->icon_action('delete')
|
||
->url('bank_statements/delete')
|
||
->class('delete_link');
|
||
}
|
||
|
||
$grid->datasource($bank_statements);
|
||
|
||
... | ... | |
}
|
||
|
||
/**
|
||
* Deletes bank statement. Only bank statements without transfers can be deleted.
|
||
* These can be created due to errors and so on.
|
||
* Deletes bank statement including all transfers created during import.
|
||
*
|
||
* @author Jiri Svitak
|
||
* @param integer $bank_statement_id
|
||
... | ... | |
Controller::error(ACCESS);
|
||
|
||
$bts = $statement->bank_transfers;
|
||
|
||
if (count($bts) > 0)
|
||
|
||
$dtids = array();
|
||
$itids = array();
|
||
$btids = array();
|
||
foreach($bts as $bank_transfer)
|
||
{
|
||
status::warning('Error - cannot delete bank statement.');
|
||
url::redirect($link_back);
|
||
$bt = new Bank_transfer_Model($bank_transfer->id);
|
||
$transfer = new Transfer_Model($bt->transfer_id);
|
||
$dependent = $transfer->get_dependent_transfers($transfer->id);
|
||
foreach($dependent as $d)
|
||
{
|
||
$dtids[] = $d->id;
|
||
}
|
||
$itids[] = $transfer->id;
|
||
$bt_model = new Bank_transfer_Model($bt->id);
|
||
$btids[] = $bt->id;
|
||
}
|
||
|
||
if ($statement->delete())
|
||
$itids = array_unique($itids);
|
||
$dtids = array_diff($dtids, $itids);
|
||
$btids = array_unique($btids);
|
||
//print_r($dtids);
|
||
//print_r($btids);
|
||
//print_r($itids);
|
||
//die();
|
||
try
|
||
{
|
||
status::error('Bank statement has been successfully deleted.');
|
||
$db = new Transfer_Model();
|
||
$db->transaction_start();
|
||
// delete dependent transfers
|
||
foreach($dtids as $dtid)
|
||
{
|
||
Transfer_Model::delete_transfer($dtid);
|
||
}
|
||
foreach($btids as $btid)
|
||
{
|
||
$btm = new Bank_transfer_Model($btid);
|
||
$btm->delete_trowable();
|
||
}
|
||
// delete independent transfers
|
||
foreach($itids as $itid)
|
||
{
|
||
Transfer_Model::delete_transfer($itid);
|
||
}
|
||
$statement->delete_trowable();
|
||
$db->transaction_commit();
|
||
status::success('Bank statement (including %d transfers and %d bank transfers) has been successfully deleted.',
|
||
true, array(0 => (count($itids) + count($dtids)), 1 => count($btids))
|
||
);
|
||
}
|
||
else
|
||
catch (Exception $e)
|
||
{
|
||
$db->transaction_rollback();
|
||
status::error('Error - cannot delete bank statement.');
|
||
}
|
||
|
||
url::redirect($link_back);
|
||
}
|
||
|
freenetis/branches/testing/application/controllers/import.php | ||
---|---|---|
*
|
||
*/
|
||
|
||
require_once APPPATH."libraries/importers/Raiffeisenbank/RB_Importer.php";
|
||
require_once APPPATH."libraries/importers/Raiffeisenbank/Parser_Ebanka.php";
|
||
require_once APPPATH."libraries/importers/Fio/FioImport.php";
|
||
require_once APPPATH."libraries/importers/Fio/FioSaver.php";
|
||
|
||
class Statistics
|
||
{
|
||
|
||
... | ... | |
*/
|
||
class Import_Controller extends Controller
|
||
{
|
||
// static constants of bank listing types
|
||
const HTML_RAIFFEISENBANK = 1;
|
||
const CSV_FIO = 2;
|
||
const CSV_POSTOVNI_SPORITELNA = 3;
|
||
|
||
private static $types = array();
|
||
|
||
/**
|
||
* Contruct - check if upload dir is writable
|
||
*/
|
||
... | ... | |
{
|
||
parent::__construct();
|
||
|
||
// supported bank listings
|
||
self::$types = array();
|
||
self::$types[self::HTML_RAIFFEISENBANK] = 'HTML Raiffeisenbank';
|
||
self::$types[self::CSV_FIO] = 'CSV Fio';
|
||
|
||
if (!is_writable('upload'))
|
||
{
|
||
Controller::error(WRITABLE, __(
|
||
'Directory "upload" is not writable, change access rights.'
|
||
));
|
||
}
|
||
|
||
}
|
||
|
||
// static constants of bank listing types
|
||
const HTML_EBANKA = 1;
|
||
const CSV_POSTOVNI_SPORITELNA = 2;
|
||
|
||
// private variables for accounts
|
||
private $bank_fees;
|
||
private $suppliers;
|
||
private $operating;
|
||
private $member_fees;
|
||
|
||
/**
|
||
* Obsahuje data o parsovaném bank. účtu (z tabluky bank_accounts)
|
||
* Používá se v callback funkci store_transfer
|
||
*
|
||
* @var Account_Model
|
||
*/
|
||
protected $parsed_bank_acc = NULL;
|
||
protected $time_now;
|
||
protected $stats;
|
||
|
||
/**
|
||
* Var for saving id of bank statement
|
||
*
|
||
* @var integer
|
||
*/
|
||
protected $bank_statement_id;
|
||
|
||
/**
|
||
* Automatic redirect
|
||
*/
|
||
public function index()
|
||
... | ... | |
}
|
||
|
||
/**
|
||
* Function uploads bank files.
|
||
*
|
||
* Uploads bank files.
|
||
*
|
||
* @author Jiri Svitak
|
||
* @param integer $id
|
||
*/
|
||
public function upload_bank_file($id = null)
|
||
... | ... | |
if ($bank_acc_model->id == 0)
|
||
Controller::error(RECORD);
|
||
|
||
// file types of bank listings
|
||
$types = array();
|
||
$types[self::HTML_EBANKA] = 'HTML eBanka';
|
||
|
||
|
||
// form
|
||
$form = new Forge(
|
||
url_lang::base() . "import/upload_bank_file/$id", '',
|
||
... | ... | |
|
||
$form->dropdown('type')
|
||
->label(__('File type') . ':')
|
||
->options($types)
|
||
->options(self::$types)
|
||
->rules('required');
|
||
|
||
$form->upload('listing', TRUE)
|
||
->label(__('File with bank transfer listing') . ':')
|
||
->rules('required');
|
||
|
||
$form->group('')
|
||
->label(__('Information from header of bank statement'));
|
||
|
||
$form->input('statement_number')
|
||
->label(__('Statement number') . ':');
|
||
|
||
$form->date('from')
|
||
->label(__('Date from') . ':')
|
||
->years(date('Y') - 100, date('Y'))
|
||
->rules('required');
|
||
|
||
$form->date('to')
|
||
->label(__('Date to') . ':')
|
||
->years(date('Y') - 100, date('Y'))
|
||
->rules('required');
|
||
|
||
$form->input('opening_balance')
|
||
->label(__('Opening balance') . ':');
|
||
|
||
$form->input('closing_balance')
|
||
->label(__('Closing balance') . ':');
|
||
|
||
|
||
$form->submit(__('Submit'));
|
||
|
||
special::required_forge_style($form, ' *', 'required');
|
||
... | ... | |
{
|
||
$form_data[$key] = htmlspecialchars($value);
|
||
}
|
||
// save bank statement
|
||
$statement = new Bank_statement_Model();
|
||
$statement->set_logger(FALSE);
|
||
$statement->bank_account_id = $id;
|
||
$statement->user_id = $this->session->get('user_id');
|
||
$statement->statement_number = $form_data['statement_number'];
|
||
$statement->from = date('Y-m-d', $form_data['from']);
|
||
$statement->to = date('Y-m-d', $form_data['to']);
|
||
$statement->opening_balance = $form_data['opening_balance'];
|
||
$statement->closing_balance = $form_data['closing_balance'];
|
||
|
||
try
|
||
switch ($form_data['type'])
|
||
{
|
||
$db = new Transfer_Model();
|
||
$db->transaction_start();
|
||
switch ($form_data['type'])
|
||
{
|
||
case self::HTML_EBANKA:
|
||
$statement->extension = "html";
|
||
$statement->save_throwable();
|
||
$this->bank_statement_id = $statement->id;
|
||
case self::HTML_RAIFFEISENBANK:
|
||
$this->parse_ebank_account($id, $form->listing->value);
|
||
break;
|
||
case self::CSV_FIO:
|
||
$this->import_fio($id, $form->listing->value);
|
||
break;
|
||
default:
|
||
break;
|
||
}
|
||
// clean temporary whitelist, members should have payed, now
|
||
// they are no longer protected from redirection messages by whitelisting
|
||
$ip_model = new Ip_address_Model();
|
||
$ip_model->clean_temporary_whitelist();
|
||
|
||
$users_contacts_model = new Users_contacts_Model();
|
||
$users_contacts_model->clean_temporary_whitelist();
|
||
// confirm whole transaction
|
||
$db->transaction_commit();
|
||
}
|
||
catch (Duplicity_Exception $e)
|
||
{
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed.') . ' ' .
|
||
__('Bank statement contains items that were already imported.'),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
catch (Exception $e)
|
||
{
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed') . '.<br>' . $e->getMessage(),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
}
|
||
else
|
||
{
|
||
... | ... | |
}
|
||
|
||
/**
|
||
* Funkce store_transfers_ebanka se používá jako callback funkce pro Parser_Ebanka.
|
||
* Třída Parser_Ebanka tuto funkci volá s každou načtenou položkou výpisu.
|
||
* Jednotlivé položky se pak uvnitř této funkce ukládají do databáze.
|
||
* Viz http://wiki.freenetis.slfree.net/index.php/Soubor:Hospodareni.pdf
|
||
*
|
||
* @author Tomas Dulik
|
||
* @param data - objekt s následujícími položkami:
|
||
* parsed_acc_nr => 184932848 //cislo parsovaneho uctu
|
||
* parsed_acc_bank_nr=> 2400 //cislo banky parsovaneho uctu
|
||
* number => 1 //cislo vypisu
|
||
* date_time => 2008-03-25 05:40 //datum a cas
|
||
* comment => Rozpis polozek uveden v soupisu prevodu
|
||
* name => CESKA POSTA, S.P.
|
||
* account_nr => 160987123
|
||
* account_bank_nr = 0300
|
||
* type => Příchozí platba
|
||
* variable_symbol => 9081000001
|
||
* constant_symbol => 998
|
||
* specific_symbol => 9876543210
|
||
* amount => 720.00
|
||
* fee => -6.90
|
||
*
|
||
* Imports fio bank listing items from specified file.
|
||
* @author Jiri Svitak
|
||
* @param <type> $file_url
|
||
*/
|
||
public function store_transfer_ebanka($data = null)
|
||
private function import_fio($bank_account_id, $file_url)
|
||
{
|
||
// param check
|
||
if (!$data || !is_object($data))
|
||
$ba = new Bank_account_Model($bank_account_id);
|
||
// parse bank listing items
|
||
$data = FioImport::getDataFromFile($file_url);
|
||
// check validity of bank listing
|
||
$header = FioImport::getListingHeader();
|
||
$ba = new Bank_account_Model($bank_account_id);
|
||
if ($ba->account_nr != $header["account_nr"] && $ba->bank_nr != $header["bank_nr"])
|
||
{
|
||
Controller::warning(PARAMETER);
|
||
$ba_nr = $ba->account_nr."/".$ba->bank_nr;
|
||
$listing_ba_nr = $header["account_nr"]."/".$header["bank_nr"];
|
||
throw new FioException(__("Bank account number in listing (%s) header does not match bank account %s in database!", array($listing_ba_nr, $ba_nr)));
|
||
}
|
||
/** zde jsou statické objekty, jejichž instance tvořím jen jednou u importu prvního řádku
|
||
* výpisu (šetříme paměť...)
|
||
* */
|
||
static $acc_model, $bank_acc_model, $member_model, $fee_model, $parsed_acc;
|
||
static $bank_interests, $bank_fees, $time_deposits_interests, $time_deposits;
|
||
static $suppliers, $operating, $member_fees, $cash;
|
||
static $first_pass = true;
|
||
try
|
||
{
|
||
$db = new Transfer_Model();
|
||
$db->transaction_start();
|
||
|
||
$this->stats->linenr++;
|
||
|
||
if ($first_pass)
|
||
{ // dostavame prvni radek vypisu?
|
||
$this->stats = new Statistics();
|
||
$this->time_now = date("Y-m-d H:i:s");
|
||
$member_model = new Member_Model(); // vytvorime vsechny instance, ktere potrebujeme i pro dalsi radky
|
||
$acc_model = new Account_Model();
|
||
$bank_acc_model = new Bank_account_Model();
|
||
$fee_model = new Fee_Model();
|
||
$ebank_nrs = array("2400", "5500");
|
||
if (!isset($this->parsed_bank_acc))
|
||
{ // mame jiz parsovany ucet v DB?
|
||
// (tato promenna bude nastavena pouze pokud se parsuje
|
||
// ucet zvoleny v gridu uzivatelem)
|
||
// parsovany ucet dopredu nezname. Je v parsovanem vypisu?
|
||
// (je, pokud to neni transparentni vypis ebanky)
|
||
if (isset($data->parsed_acc_nr) && isset($data->parsed_acc_bank_nr))
|
||
{
|
||
if (in_array($data->parsed_acc_bank_nr, $ebank_nrs))
|
||
// u ebanky probehla zmena kodu banky...
|
||
$bank_nr = "5500 (2400)";
|
||
else
|
||
$bank_nr=$data->parsed_acc_bank_nr;
|
||
|
||
$this->parsed_bank_acc = ORM::factory('bank_account')
|
||
->where(array
|
||
(
|
||
'account_nr' => $data->parsed_acc_nr,
|
||
'bank_nr' => $bank_nr
|
||
))->find();
|
||
|
||
if (!$this->parsed_bank_acc->id)
|
||
{ // parsovany ucet zatim neexistuje?
|
||
// tak si ho vytvorime
|
||
$acc_name = "$data->parsed_acc_nr/$bank_nr";
|
||
$parsed_acc = Account_Model::create(
|
||
Account_attribute_Model::$bank, $acc_name, 1
|
||
);
|
||
$this->parsed_bank_acc = Bank_account_Model::create(
|
||
$acc_name, $data->parsed_acc_nr, $bank_nr, 1
|
||
);
|
||
$parsed_acc->add($this->parsed_bank_acc);
|
||
}
|
||
}
|
||
else
|
||
{ // if (isset($data->parsed_acc_nr) ... ve výpisu není číslo parsovaného účtu = kritická chyba
|
||
status::error('The parsed account is unknown.');
|
||
return;
|
||
}
|
||
}
|
||
else if (isset($data->parsed_acc_nr) && isset($data->parsed_acc_bank_nr) &&
|
||
!($data->parsed_acc_nr == $this->parsed_bank_acc->account_nr && // cisla uctu odpovidaji
|
||
($data->parsed_acc_bank_nr == $this->parsed_bank_acc->bank_nr || // cisla bank odpovidaji nebo
|
||
in_array($data->parsed_acc_bank_nr, $ebank_nrs) && // jsou obe 2400 nebo 5500
|
||
in_array($this->parsed_bank_acc->bank_nr, $ebank_nrs)
|
||
)))
|
||
{
|
||
throw new Kohana_User_Exception('Chyba', 'Importovaný výpis není z vybraného účtu!');
|
||
}
|
||
|
||
// @todo tato chyba nema byt Exception, ale normalni hlášení
|
||
if (!isset($parsed_acc))
|
||
{
|
||
$parsed_acc = $this->parsed_bank_acc->get_related_account_by_attribute_id(
|
||
Account_attribute_Model::$bank
|
||
);
|
||
|
||
if ($parsed_acc === FALSE)
|
||
// tohle by normálně nemělo nastat.
|
||
// může se to stát pouze pokud někdo smaže vazbu bank. účet sdružení
|
||
// s podvojným účtem přes tabulku accounts_bank_accounts
|
||
throw new Kohana_User_Exception(
|
||
'Kritická chyba',
|
||
'V tabulce accounts_bank_accounts chybí vazba ' .
|
||
'bankovního a podvojného účtu sdružení'
|
||
);
|
||
}
|
||
// Teď potřebujeme najít nebo vytvořit speciální podvojné účty k parsovanému bank. učtu:
|
||
$spec_accounts = array(
|
||
"bank_interests" => "Úroky z $parsed_acc->name",
|
||
"time_deposits_interests" => "Úroky z termín. vkladů $parsed_acc->name",
|
||
"time_deposits" => "Termínované vklady $parsed_acc->name",
|
||
"bank_fees" => "Poplatky z $parsed_acc->name"
|
||
);
|
||
|
||
foreach ($spec_accounts as $accnt => $name)
|
||
{
|
||
$spec_acc = $this->parsed_bank_acc->get_related_account_by_attribute_id(
|
||
Account_attribute_Model::$$accnt
|
||
);
|
||
|
||
if (!$spec_acc || !$spec_acc->id)
|
||
{ // pokud spec. ucet neexistuje, pak si jej vytvorime
|
||
$spec_acc = Account_Model::create(Account_attribute_Model::$$accnt, $name, 1);
|
||
$spec_acc->add($this->parsed_bank_acc);
|
||
}
|
||
$$accnt = $spec_acc;
|
||
}
|
||
// save bank statement
|
||
$statement = new Bank_statement_Model();
|
||
$statement->set_logger(FALSE);
|
||
$statement->bank_account_id = $bank_account_id;
|
||
$statement->user_id = $this->session->get('user_id');
|
||
$statement->type = self::$types[self::CSV_FIO];
|
||
$statement->from = $header["from"];
|
||
$statement->to = $header["to"];
|
||
$statement->opening_balance = $header["opening_balance"];
|
||
$statement->closing_balance = $header['closing_balance'];
|
||
$statement->save_throwable();
|
||
|
||
$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();
|
||
// save bank listing items
|
||
$stats = FioSaver::save($data, $bank_account_id, $statement->id, $this->session->get('user_id'));
|
||
|
||
if (!$suppliers->id || !$member_fees->id || !$operating->id)
|
||
{
|
||
throw new Kohana_User_Exception(
|
||
'Kritická chyba',
|
||
'V DB chybí účet member_fees, suppliers nebo operating'
|
||
);
|
||
}
|
||
|
||
$first_pass = FALSE;
|
||
// clean temporary whitelist, members should have payed, now
|
||
// they are no longer protected from redirection messages by whitelisting
|
||
$ip_model = new Ip_address_Model();
|
||
$ip_model->clean_temporary_whitelist();
|
||
$users_contacts_model = new Users_contacts_Model();
|
||
$users_contacts_model->clean_temporary_whitelist();
|
||
|
||
$db->transaction_commit();
|
||
}
|
||
|
||
if (!empty($data->fee))
|
||
catch (FioException $e)
|
||
{
|
||
$fee = abs($data->fee);
|
||
$this->stats->bank_fees+= - $data->fee;
|
||
$this->stats->bank_fees_nr++;
|
||
$db->transaction_rollback();
|
||
status::error($e->getMessage());
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
|
||
// ********************** Tak a jdeme tvořit transakce *********************
|
||
$vs = trim($data->variable_symbol);
|
||
if (empty($data->amount))
|
||
catch (Duplicity_Exception $e)
|
||
{
|
||
// ****** Bankovní poplatky: ebanka má v řádku výpisu pouze poplatek, ale castka==0
|
||
// vytvoříme transakci "bankovní poplatek z 221000 (bank. účty) na 549001 (bank. poplatky)
|
||
//a bankovní transakci z parsovaného účtu na null. Přiřadíme ji sdružení (member_id=1).
|
||
if (empty($data->comment))
|
||
$data->comment = $data->type;
|
||
if ($data->fee < 0)
|
||
{
|
||
$this->create_transfers(
|
||
$parsed_acc, $bank_fees, $fee,
|
||
$this->parsed_bank_acc, null, $data, 1
|
||
);
|
||
}
|
||
else // poplatek>0 - storno poplatku (stalo se 1x v celé historii našeho sdružení)
|
||
{
|
||
$this->create_transfers(
|
||
$bank_fees, $parsed_acc, $fee,
|
||
$this->parsed_bank_acc, null, $data, 1
|
||
);
|
||
}
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed.') . ' ' .
|
||
__('Bank statement contains items that were already imported.'),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
// castka je nenulova:
|
||
else if (empty($data->fee) && stripos($data->type, "rok") !== FALSE)
|
||
catch (Exception $e)
|
||
{
|
||
// ***** úroky u ebanky: amount!=0, fee==0, type je "Úrok", "Kladný úrok", "Převedení úroku po kapitalizaci TV"
|
||
// Vytvoříme transakci z 644000 nebo 655000 (uroky) na 221000
|
||
// a bankovní transakci z null na parsovaný účet. Přiřadíme ji sdružení (member_id=1)
|
||
if (empty($vs)) // běžný úrok? (644000)
|
||
{
|
||
$this->create_transfers(
|
||
$bank_interests, $parsed_acc, $data->amount,
|
||
null, $this->parsed_bank_acc, $data, 1
|
||
);
|
||
}
|
||
else // úrok z termínovaného vkladu (655000)
|
||
{
|
||
$this->create_transfers(
|
||
$time_deposits_interests, $parsed_acc, $data->amount,
|
||
null, $this->parsed_bank_acc, $data, 1
|
||
);
|
||
}
|
||
|
||
$this->stats->interests += $data->amount;
|
||
$this->stats->interests_nr++;
|
||
} else
|
||
{
|
||
// ****** nejběžnější případ:
|
||
// - členský příspěvek, platba faktury dodavatelum, termín. vklad, výběr hotovosti ******
|
||
// Nejdriv zkusím najít majitele bankovního protiúčtu
|
||
$ks = trim($data->constant_symbol);
|
||
$term_vklad = ($ks == "968");
|
||
$member_model->clear();
|
||
$member = $member_model;
|
||
if (!$term_vklad && $data->amount > 0 && !empty($vs))
|
||
{ //u čl. příspěvků zkusíme najít původce:
|
||
// členský příspěvek nebo příjem z faktury odběrateli
|
||
// @todo zpracování jiných typů VS u člen. příspěvků (např. ID+CRC16)
|
||
// uvedl člen u teto platby jako variabilni symbol (VS) svůj telefon ?
|
||
$member = $member_model->where('variable_symbol', $vs)->find();
|
||
|
||
if (!$member->id)
|
||
{
|
||
$member = $member_model->find_member_by_phone($vs);
|
||
if (!$member || !$member->id)
|
||
{ // find_member_by_phone vrací false, pokud nic nenajde
|
||
// ne? zkusime, zda jako VS nezadal svoje member_id + crc16(member_id)
|
||
// $member=$member_model->get_member_by_crc_id($vs);
|
||
// this function has bugs!!! needs rewrite!!!
|
||
$member = $member_model;
|
||
$this->stats->unidentified_transfers++;
|
||
}
|
||
}
|
||
}
|
||
// else { // if platba přijaté faktury - majitele účtu najdeme dle VS na faktuře, až budeme mít modul přijatých faktur}
|
||
// ***Tady si vytvorime instanci účtu clena (nebo dodavatele) z prave nacteneho vypisu:
|
||
$bank_acc = $bank_acc_model->where(array
|
||
(
|
||
'account_nr' => $data->account_nr,
|
||
'bank_nr' => $data->account_bank_nr
|
||
))->find();
|
||
|
||
if (!$bank_acc->id)
|
||
{ // bank. ucet clena neexistuje, tak si ho vytvorime
|
||
$bank_acc->clear();
|
||
$bank_acc->set_logger(FALSE);
|
||
//term. vklad je vždy způsoben sdružením
|
||
$member_idd = ($term_vklad ? 1 : $member->id);
|
||
$bank_acc->member_id = $member_idd != 0 ? $member_idd : NULL;
|
||
$bank_acc->name = $data->name;
|
||
$bank_acc->account_nr = $data->account_nr;
|
||
$bank_acc->bank_nr = $data->account_bank_nr;
|
||
$bank_acc->save();
|
||
$this->stats->new_bank_accounts++;
|
||
//tuto vazbu bych tvořil jen pokud bych chtěl evidovat pohyby na bank. účtech členů
|
||
// $bank_acc->add_account($member_fees);
|
||
}
|
||
if ($data->amount < 0)
|
||
{
|
||
$amount = abs($data->amount);
|
||
if ($term_vklad)
|
||
{ // převod peněz na účet term. vkladu
|
||
$id = $this->create_transfers(
|
||
$parsed_acc, $time_deposits, $amount,
|
||
$this->parsed_bank_acc, $bank_acc,
|
||
$data, $member->id, null
|
||
);
|
||
$this->stats->time_deposits+=$amount;
|
||
$this->stats->time_deposits_nr++;
|
||
}
|
||
else
|
||
{
|
||
if (stripos($data->type, "hotovost") !== FALSE)
|
||
{ // výběr do pokladny ?
|
||
$id = $this->create_transfers(
|
||
$parsed_acc, $cash, $amount,
|
||
$this->parsed_bank_acc, null,
|
||
$data, $member->id, null
|
||
);
|
||
$this->stats->cash_drawn+=$amount;
|
||
$this->stats->cash_drawn_nr++;
|
||
}
|
||
else
|
||
{
|
||
// úhrada faktury - z 221000 (bank. účet) na 321000 (dodavatelé)
|
||
// pokud se předtím nepodařilo najít majitele dle VS
|
||
if (!$member->id && $bank_acc->member_id)
|
||
{ // zkusím ho vzít odsud
|
||
$member = $member_model->find($bank_acc->member_id);
|
||
}
|
||
|
||
$id = $this->create_transfers(
|
||
$parsed_acc, $suppliers, $amount,
|
||
$this->parsed_bank_acc, $bank_acc,
|
||
$data, $member->id
|
||
);
|
||
|
||
$this->stats->invoices+=$amount;
|
||
$this->stats->invoices_nr++;
|
||
}
|
||
} // if ($term_vklad) ... else
|
||
if (!empty($fee))
|
||
{
|
||
// je tam bankovní poplatek - vytvoříme:
|
||
// - podvojnou transakci z 221000 (bank. účty) na 549001 (bank. poplatky)
|
||
// - bankovní transakci z parsovaného účtu na null
|
||
$data->comment = "Bank. poplatek" . (!empty($data->comment) ? " ($data->comment)" : "");
|
||
$this->create_transfers(
|
||
$parsed_acc, $bank_fees, $fee, $this->parsed_bank_acc,
|
||
null, $data, $member->id, $id
|
||
);
|
||
}
|
||
}
|
||
else
|
||
{ // $data->amount > 0
|
||
if ($term_vklad)
|
||
{ // stažení peněz z účtu term. vkladu
|
||
$id = $this->create_transfers(
|
||
$time_deposits, $parsed_acc, $data->amount,
|
||
$bank_acc, $this->parsed_bank_acc,
|
||
$data, $member->id, null
|
||
);
|
||
$this->stats->time_deposits_drawn+=$data->amount;
|
||
$this->stats->time_deposits_drawn_nr++;
|
||
}
|
||
else
|
||
{
|
||
// členský příspěvek - vytvoříme:
|
||
// - podvojnou transakci z 684000 na 221000
|
||
// - bankovní transakci z bank. účtu člena na bank. účet sdružení
|
||
$id = $this->create_transfers(
|
||
$member_fees, $parsed_acc, $data->amount, $bank_acc,
|
||
$this->parsed_bank_acc, $data, $member->id
|
||
);
|
||
|
||
if (!empty($fee))
|
||
{
|
||
// bankovní poplatek - vytvoříme:
|
||
// - podvojnou transakci z 221000 (bank. účty) na 549001 (bank. poplatky)
|
||
// - bankovní transakci z parsovaného účtu na null
|
||
$data->comment = "Bank. poplatek" . (!empty($data->comment) ? " ($data->comment)" : "");
|
||
$this->create_transfers(
|
||
$parsed_acc, $bank_fees, $fee,
|
||
$this->parsed_bank_acc, null, $data,
|
||
$member->id, $id
|
||
);
|
||
// naše správní rada si vymyslela, že poplatek budeme dotovat z operačního účtu
|
||
// (pokud máte ve správní radě rozumnější lidi, tak tento řádek zakomentujte :-)
|
||
$this->create_transfer(
|
||
$operating, $parsed_acc, abs($data->fee),
|
||
$data->date_time, "Bank. poplatek hrazený sdružením",
|
||
$member->id, $id
|
||
);
|
||
}
|
||
|
||
if ($member->id)
|
||
{ // původce je známý?
|
||
// **** převedeme peníze členovi na jeho účet s kreditem
|
||
// ten účet ale musíme najít nebo vytvořit:
|
||
$credit_acc = $acc_model->where(array
|
||
(
|
||
'member_id' => $member->id,
|
||
'account_attribute_id' => Account_attribute_Model::$credit)
|
||
)->find();
|
||
|
||
if (!$credit_acc->id)
|
||
{
|
||
$credit_acc->clear();
|
||
$credit_acc->set_logger(FALSE);
|
||
$credit_acc->account_attribute_id = Account_attribute_Model::$credit;
|
||
$credit_acc->member_id = $member->id;
|
||
/**
|
||
* @todo Jirka pri tvorbe uctu jako jmeno uctu pouziva
|
||
* prijmeni jmeno majitele. To se mi tady nechce programovat,
|
||
* protoze se jen tezko muze stat, ze by kreditni ucet neexistoval
|
||
*/
|
||
$credit_acc->name = $member->name;
|
||
$credit_acc->save();
|
||
}
|
||
|
||
$this->create_transfer(
|
||
$parsed_acc, $acc_model, $data->amount,
|
||
$data->date_time, "Přiřazení platby",
|
||
$member->id, $id
|
||
);
|
||
|
||
// teď se podíváme, jestli v té době sdružení
|
||
// účtovalo poplatek za zpracování platby:
|
||
$fee = $fee_model->get_by_date_type(
|
||
$data->date_time, 'transfer fee'
|
||
);
|
||
|
||
if (is_object($fee) && $fee->id) // ano? Pak poplatek strhneme z účtu
|
||
{
|
||
$this->create_transfer(
|
||
$credit_acc, $operating, $fee->fee,
|
||
$data->date_time, "Transakční poplatek",
|
||
$member->id, $id
|
||
);
|
||
}
|
||
} // if (is_object($member) && $member->id)
|
||
$this->stats->member_fees_nr++;
|
||
$this->stats->member_fees+=$data->amount;
|
||
} // if ($term_vklad) ... else {
|
||
} // else { // $data->amount > 0
|
||
} // else { // ****** castka!=0 && poplatek !=0
|
||
}
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed') . '.<br>' . $e->getMessage(),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
|
||
/**
|
||
* Creates transfer
|
||
*
|
||
* @param Account_Model $src
|
||
* @param Account_Model $dst
|
||
* @param double $amount
|
||
* @param string $datetime
|
||
* @param string $text
|
||
* @param integer $member_id
|
||
* @param integer $prev_id
|
||
* @return integer ID of created transfer or FALSe on error
|
||
*/
|
||
private function create_transfer(
|
||
$src, $dst, $amount, $datetime, $text, $member_id=null, $prev_id=null)
|
||
{
|
||
// safe transfer saving
|
||
return Transfer_Model::insert_transfer(
|
||
$src->id, $dst->id, $prev_id, $member_id,
|
||
$this->session->get('user_id'), null,
|
||
$datetime, $this->time_now, $text, $amount
|
||
);
|
||
url::redirect(url_lang::base().'bank_transfers/show_by_bank_statement/'.$statement->id);
|
||
}
|
||
|
||
/**
|
||
*
|
||
* @param Account_Model $src_acc
|
||
* @param Account_Model $dst_acc
|
||
* @param double $amount
|
||
* @param type $src_bank_acc
|
||
* @param type $dst_bank_acc
|
||
* @param type $data
|
||
* @param type $member_id
|
||
* @param type $prev_id
|
||
* @return integer
|
||
*/
|
||
private function create_transfers(
|
||
$src_acc, $dst_acc, $amount, $src_bank_acc,
|
||
$dst_bank_acc, $data, $member_id=null, $prev_id=null)
|
||
{
|
||
// duplicity check - in case of duplicity all already imported items are storned
|
||
$bank_transfer = new Bank_transfer_Model();
|
||
$bank_transfer->set_logger(FALSE);
|
||
$dups = $bank_transfer->get_duplicities($data);
|
||
|
||
if ($dups->count() > 0)
|
||
throw new Duplicity_Exception();
|
||
|
||
// safe transfer saving
|
||
$transfer_id = Transfer_Model::insert_transfer(
|
||
$src_acc->id, $dst_acc->id, $prev_id,
|
||
$member_id, $this->session->get('user_id'),
|
||
null, $data->date_time, $this->time_now,
|
||
$data->comment, $amount
|
||
);
|
||
// bank transfer saving
|
||
$bank_transfer->clear();
|
||
$bank_transfer->transfer_id = $transfer_id;
|
||
$bank_transfer->origin_id = isset($src_bank_acc) ? $src_bank_acc->id : null;
|
||
$bank_transfer->destination_id = isset($dst_bank_acc) ? $dst_bank_acc->id : null;
|
||
$bank_transfer->bank_statement_id = $this->bank_statement_id;
|
||
$bank_transfer->number = $data->number;
|
||
$bank_transfer->variable_symbol = $data->variable_symbol;
|
||
$bank_transfer->constant_symbol = $data->constant_symbol;
|
||
$bank_transfer->specific_symbol = $data->specific_symbol;
|
||
$bank_transfer->save_throwable();
|
||
|
||
return $transfer_id;
|
||
}
|
||
|
||
/**
|
||
* Parse ebank account
|
||
*
|
||
* @author Tomas Dulik, Jiri Svitak
|
||
* @author Jiri Svitak
|
||
* @param integer $account_id ID of the account whose data will be parsed.
|
||
* @param string $url URL containing the file to parse
|
||
*/
|
||
private function parse_ebank_account($bank_account_id = NULL, $url = '')
|
||
private function parse_ebank_account($bank_account_id, $url)
|
||
{
|
||
//$this->profiler = new Profiler;
|
||
$mem = memory_get_usage();
|
||
$start = microtime(true);
|
||
|
||
$parser = new Parser_Ebanka();
|
||
$parser->set_callback(array($this, 'store_transfer_ebanka'));
|
||
|
||
// pokud z gridu dostáváme $account_id, pak si ho uložíme pro potřeby callback funkce
|
||
if (isset($bank_account_id))
|
||
$this->parsed_bank_acc = new Bank_account_Model($bank_account_id);
|
||
try
|
||
{
|
||
$db = new Transfer_Model();
|
||
$db->transaction_start();
|
||
|
||
// safe import is done by transaction processing started in method which called this one
|
||
$parser->parse($url);
|
||
$parser = new Parser_Ebanka();
|
||
if (isset($bank_account_id))
|
||
{
|
||
$parsed_bank_acc = new Bank_account_Model($bank_account_id);
|
||
RB_Importer::$parsed_bank_acc = $parsed_bank_acc;
|
||
}
|
||
|
||
$stats = $this->stats;
|
||
$stats_table = new View('table_2_columns');
|
||
$stats->memory_consumed = ((memory_get_usage() - $mem) >> 10) . " kB";
|
||
$stats->execution_time = sprintf("%.2f sec", microtime(true) - $start);
|
||
|
||
// z názvů proměnných odstraním mezery, první písmena změním na velká, přeložím:
|
||
foreach ($stats as $key => $val)
|
||
$arr_stats[__(ucfirst(strtr($key, "_", " ")))] = $val;
|
||
$stats_table->set('table_data', $arr_stats);
|
||
|
||
$summary = array
|
||
(
|
||
__('Incomes') => $stats->interests
|
||
+ $stats->member_fees
|
||
+ $stats->time_deposits_drawn,
|
||
|
||
__('Expenses') => $stats->bank_fees
|
||
+ $stats->invoices
|
||
+ $stats->time_deposits
|
||
+ $stats->cash_drawn
|
||
);
|
||
|
||
$sum_table = new View('table_2_columns');
|
||
$sum_table->set('table_data', $summary);
|
||
|
||
// breadcrubs
|
||
$breadcrumbs = breadcrumbs::add()
|
||
->link('members/show/1', 'Profile of association',
|
||
$this->acl_check_view('Members_Controller', 'members'))
|
||
->link('bank_accounts/show_all', 'Bank accounts',
|
||
$this->acl_check_view('Accounts_Controller', 'bank_accounts'))
|
||
->text($this->parsed_bank_acc->name)
|
||
->text('Import results')
|
||
->html();
|
||
|
||
// links
|
||
$transfers_link = html::anchor(
|
||
url_lang::base() . 'bank_transfers/show_by_bank_statement/' .
|
||
$this->bank_statement_id,
|
||
__('Transfers of bank statement')
|
||
);
|
||
$statement = new Bank_statement_Model();
|
||
$statement->set_logger(FALSE);
|
||
$statement->bank_account_id = $bank_account_id;
|
||
$statement->user_id = $this->session->get('user_id');
|
||
$statement->type = self::$types[self::HTML_RAIFFEISENBANK];
|
||
$statement->save_throwable();
|
||
|
||
// view
|
||
$view = new View('main');
|
||
$title = __('Import results');
|
||
$view->title = $title;
|
||
$view->breadcrumbs = $breadcrumbs;
|
||
$view->content = new View('show_all');
|
||
$view->content->table = __("Summary") . $sum_table .
|
||
__("Statistics") . $stats_table;
|
||
$view->content->link_back = $transfers_link;
|
||
$view->content->headline = $title;
|
||
$view->render(TRUE);
|
||
RB_Importer::$bank_statement_id = $statement->id;
|
||
RB_Importer::$user_id = $this->session->get('user_id');
|
||
RB_IMporter::$time_now = date("Y-m-d H:i:s");
|
||
|
||
// safe import is done by transaction processing started in method which called this one
|
||
$parser->parse($url);
|
||
|
||
// save statement's from and to
|
||
$statement->from = $parser->from;
|
||
$statement->to = $parser->to;
|
||
$statement->save_throwable();
|
||
|
||
// clean temporary whitelist, members should have payed, now
|
||
// they are no longer protected from redirection messages by whitelisting
|
||
$ip_model = new Ip_address_Model();
|
||
$ip_model->clean_temporary_whitelist();
|
||
$users_contacts_model = new Users_contacts_Model();
|
||
$users_contacts_model->clean_temporary_whitelist();
|
||
|
||
$db->transaction_commit();
|
||
}
|
||
catch (Duplicity_Exception $e)
|
||
{
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed.') . ' ' .
|
||
__('Bank statement contains items that were already imported.'),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
catch (Exception $e)
|
||
{
|
||
$db->transaction_rollback();
|
||
status::error(
|
||
__('Import has failed') . '.<br>' . $e->getMessage(),
|
||
FALSE
|
||
);
|
||
url::redirect(url_lang::base() . 'bank_accounts/show_all');
|
||
}
|
||
|
||
url::redirect(url_lang::base().'bank_transfers/show_by_bank_statement/'.$statement->id);
|
||
|
||
}
|
||
|
||
}
|
freenetis/branches/testing/application/upgrade_sql/upgrade_sql.php | ||
---|---|---|
|
||
|
||
/**
|
||
* Creates port number from their names
|
||
* Statements changed from extension to particular bank statement type.
|
||
*
|
||
* @author Michal Kliment
|
||
* @return boolean
|
||
* @author Jiri Svitak
|
||
*/
|
||
function upgrade_sql_after()
|
||
{
|
||
$port_model = new Port_Model();
|
||
|
||
$ports = $port_model->find_all();
|
||
|
||
foreach ($ports as $port)
|
||
{
|
||
$arr = explode(" ", $port->name);
|
||
$nr = array_pop($arr);
|
||
|
||
if (is_numeric($nr))
|
||
{
|
||
$port->port_nr = $nr;
|
||
$port->save();
|
||
}
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* Array of sql queries that upgrade database.
|
||
* Adds VoIP views to database for synchronizing with SIP server.
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
$upgrade_sql[get_SVN_rev()] = array
|
||
(
|
||
/* Drops old tables */
|
||
|
||
"ALTER TABLE `ifaces` ADD `type` INT( 11 ) NOT NULL AFTER `id` , ADD INDEX ( `type` );",
|
||
|
||
"UPDATE ifaces i, segments s SET i.type = 1 WHERE i.segment_id = s.id AND medium_id = 2;",
|
||
|
||
"UPDATE ifaces i, segments s SET i.type = 2 WHERE i.segment_id = s.id AND medium_id = 3;",
|
||
|
||
"CREATE TABLE `wireless_ifaces` (
|
||
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
|
||
`iface_id` INT( 11 ) NOT NULL ,
|
||
`ssid` VARCHAR( 50 ) COLLATE utf8_czech_ci DEFAULT NULL ,
|
||
`wmode` INT( 11 ) NOT NULL ,
|
||
`norm` INT( 11 ) NOT NULL ,
|
||
`frequence` INT( 11 ) DEFAULT NULL ,
|
||
`channel` INT( 11 ) DEFAULT NULL ,
|
||
`antenna` INT( 11 ) NOT NULL ,
|
||
`polarization` INT( 11 ) NOT NULL ,
|
||
PRIMARY KEY ( `id` )
|
||
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;",
|
||
|
||
"INSERT INTO `wireless_ifaces` SELECT * FROM `wireless_settings` ;",
|
||
|
||
"ALTER TABLE `wireless_ifaces`
|
||
DROP `ssid`,
|
||
DROP `norm`,
|
||
DROP `frequence`,
|
||
DROP `channel`,
|
||
DROP `polarization`;",
|
||
|
||
"UPDATE wireless_ifaces wi SET wmode = wmode - 44;",
|
||
|
||
"UPDATE wireless_ifaces SET antenna = antenna - 50;",
|
||
|
||
"RENAME TABLE `wireless_settings` TO `wireless_segments`;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD `segment_id` INT( 11 ) NOT NULL AFTER `id`, ADD INDEX ( `segment_id` );",
|
||
|
||
"UPDATE wireless_segments ws, ifaces i SET ws.segment_id = i.segment_id WHERE ws.iface_id = i.id;",
|
||
|
||
"ALTER TABLE `wireless_segments` DROP FOREIGN KEY `wireless_segments_ibfk_1`;",
|
||
|
||
"ALTER TABLE `wireless_segments`
|
||
DROP `iface_id`,
|
||
DROP `wmode`,
|
||
DROP `antenna`;",
|
||
|
||
"UPDATE wireless_segments ws,
|
||
(SELECT 1 AS new, 47 AS old UNION SELECT 2 AS new, 48 AS old UNION SELECT 3 AS new, 49 AS old UNION SELECT 4 AS new, 50 AS old UNION SELECT 5 AS new, 61 AS old) n
|
||
SET ws.norm = n.new
|
||
WHERE ws.norm = n.old;",
|
||
|
||
"UPDATE wireless_segments ws,
|
||
(SELECT 1 AS new, 54 AS old UNION SELECT 2 AS new, 55 AS old UNION SELECT 3 AS new, 56 AS old UNION SELECT 4 AS new, 63 AS old UNION SELECT 5 AS new, 64 AS old) p
|
||
SET ws.polarization = p.new
|
||
WHERE ws.polarization = p.old;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD `channel_width` INT( 11 ) NULL AFTER `channel`;",
|
||
|
||
"ALTER TABLE `ports` ADD `port_nr` INT( 11 ) NULL AFTER `segment_id`, ADD INDEX ( `port_nr` );",
|
||
|
||
"ALTER TABLE `wireless_ifaces` ADD INDEX ( `iface_id` );",
|
||
|
||
"ALTER TABLE `wireless_ifaces` ADD FOREIGN KEY ( `iface_id` ) REFERENCES `ifaces` (`id`) ON DELETE CASCADE ;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD FOREIGN KEY ( `segment_id` ) REFERENCES `segments` (`id`) ON DELETE CASCADE ;",
|
||
|
||
"INSERT INTO wireless_ifaces
|
||
SELECT NULL, i.id, 2, NULL
|
||
FROM devices d
|
||
JOIN ifaces i ON i.device_id = d.id
|
||
WHERE d.type = 8 AND i.type = 1 AND i.id NOT IN (SELECT iface_id FROM wireless_ifaces);",
|
||
|
||
"INSERT INTO wireless_ifaces
|
||
SELECT NULL, i.id, 1, NULL
|
||
FROM devices d
|
||
JOIN ifaces i ON i.device_id = d.id
|
||
WHERE d.type = 62 AND i.type = 1 AND i.id NOT IN (SELECT iface_id FROM wireless_ifaces);",
|
||
"ALTER TABLE `bank_statements` CHANGE `extension` `type` VARCHAR( 40 ) CHARACTER SET utf8 COLLATE utf8_czech_ci NULL DEFAULT NULL "
|
||
);
|
freenetis/branches/testing/application/upgrade_sql/upgrade_sql_1219.php | ||
---|---|---|
<?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/
|
||
*
|
||
*/
|
||
|
||
|
||
/**
|
||
* Creates port number from their names
|
||
*
|
||
* @author Michal Kliment
|
||
* @return boolean
|
||
*/
|
||
function upgrade_sql_1219_after()
|
||
{
|
||
$port_model = new Port_Model();
|
||
|
||
$ports = $port_model->find_all();
|
||
|
||
foreach ($ports as $port)
|
||
{
|
||
$arr = explode(" ", $port->name);
|
||
$nr = array_pop($arr);
|
||
|
||
if (is_numeric($nr))
|
||
{
|
||
$port->port_nr = $nr;
|
||
$port->save();
|
||
}
|
||
}
|
||
|
||
return true;
|
||
}
|
||
|
||
/**
|
||
* Array of sql queries that upgrade database.
|
||
* Adds VoIP views to database for synchronizing with SIP server.
|
||
*
|
||
* @author Ondřej Fibich
|
||
*/
|
||
$upgrade_sql[1219] = array
|
||
(
|
||
/* Drops old tables */
|
||
|
||
"ALTER TABLE `ifaces` ADD `type` INT( 11 ) NOT NULL AFTER `id` , ADD INDEX ( `type` );",
|
||
|
||
"UPDATE ifaces i, segments s SET i.type = 1 WHERE i.segment_id = s.id AND medium_id = 2;",
|
||
|
||
"UPDATE ifaces i, segments s SET i.type = 2 WHERE i.segment_id = s.id AND medium_id = 3;",
|
||
|
||
"CREATE TABLE `wireless_ifaces` (
|
||
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
|
||
`iface_id` INT( 11 ) NOT NULL ,
|
||
`ssid` VARCHAR( 50 ) COLLATE utf8_czech_ci DEFAULT NULL ,
|
||
`wmode` INT( 11 ) NOT NULL ,
|
||
`norm` INT( 11 ) NOT NULL ,
|
||
`frequence` INT( 11 ) DEFAULT NULL ,
|
||
`channel` INT( 11 ) DEFAULT NULL ,
|
||
`antenna` INT( 11 ) NOT NULL ,
|
||
`polarization` INT( 11 ) NOT NULL ,
|
||
PRIMARY KEY ( `id` )
|
||
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_czech_ci;",
|
||
|
||
"INSERT INTO `wireless_ifaces` SELECT * FROM `wireless_settings` ;",
|
||
|
||
"ALTER TABLE `wireless_ifaces`
|
||
DROP `ssid`,
|
||
DROP `norm`,
|
||
DROP `frequence`,
|
||
DROP `channel`,
|
||
DROP `polarization`;",
|
||
|
||
"UPDATE wireless_ifaces wi SET wmode = wmode - 44;",
|
||
|
||
"UPDATE wireless_ifaces SET antenna = antenna - 50;",
|
||
|
||
"RENAME TABLE `wireless_settings` TO `wireless_segments`;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD `segment_id` INT( 11 ) NOT NULL AFTER `id`, ADD INDEX ( `segment_id` );",
|
||
|
||
"UPDATE wireless_segments ws, ifaces i SET ws.segment_id = i.segment_id WHERE ws.iface_id = i.id;",
|
||
|
||
"ALTER TABLE `wireless_segments` DROP FOREIGN KEY `wireless_segments_ibfk_1`;",
|
||
|
||
"ALTER TABLE `wireless_segments`
|
||
DROP `iface_id`,
|
||
DROP `wmode`,
|
||
DROP `antenna`;",
|
||
|
||
"UPDATE wireless_segments ws,
|
||
(SELECT 1 AS new, 47 AS old UNION SELECT 2 AS new, 48 AS old UNION SELECT 3 AS new, 49 AS old UNION SELECT 4 AS new, 50 AS old UNION SELECT 5 AS new, 61 AS old) n
|
||
SET ws.norm = n.new
|
||
WHERE ws.norm = n.old;",
|
||
|
||
"UPDATE wireless_segments ws,
|
||
(SELECT 1 AS new, 54 AS old UNION SELECT 2 AS new, 55 AS old UNION SELECT 3 AS new, 56 AS old UNION SELECT 4 AS new, 63 AS old UNION SELECT 5 AS new, 64 AS old) p
|
||
SET ws.polarization = p.new
|
||
WHERE ws.polarization = p.old;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD `channel_width` INT( 11 ) NULL AFTER `channel`;",
|
||
|
||
"ALTER TABLE `ports` ADD `port_nr` INT( 11 ) NULL AFTER `segment_id`, ADD INDEX ( `port_nr` );",
|
||
|
||
"ALTER TABLE `wireless_ifaces` ADD INDEX ( `iface_id` );",
|
||
|
||
"ALTER TABLE `wireless_ifaces` ADD FOREIGN KEY ( `iface_id` ) REFERENCES `ifaces` (`id`) ON DELETE CASCADE ;",
|
||
|
||
"ALTER TABLE `wireless_segments` ADD FOREIGN KEY ( `segment_id` ) REFERENCES `segments` (`id`) ON DELETE CASCADE ;",
|
||
|
||
"INSERT INTO wireless_ifaces
|
||
SELECT NULL, i.id, 2, NULL
|
||
FROM devices d
|
||
JOIN ifaces i ON i.device_id = d.id
|
||
WHERE d.type = 8 AND i.type = 1 AND i.id NOT IN (SELECT iface_id FROM wireless_ifaces);",
|
||
|
||
"INSERT INTO wireless_ifaces
|
||
SELECT NULL, i.id, 1, NULL
|
||
FROM devices d
|
||
JOIN ifaces i ON i.device_id = d.id
|
||
WHERE d.type = 62 AND i.type = 1 AND i.id NOT IN (SELECT iface_id FROM wireless_ifaces);",
|
||
);
|
||
|
||
?>
|
freenetis/branches/testing/application/libraries/Parser_Ebanka.php | ||
---|---|---|
<?php defined('SYSPATH') or die('No direct script access.');
|
||
|
||
require_once("Parser_Html_Table.php");
|
||
/*
|
||
ALTER TABLE `money_transfer_bank_infos`
|
||
ADD COLUMN `comment` VARCHAR(255) after `date_time`;
|
||
|
||
ALTER TABLE `accounts`
|
||
ADD COLUMN `number` VARCHAR(255) after `name`;
|
||
|
||
|
||
* */
|
||
|
||
/**
|
||
* @author Tomas <Dulik at unart dot cz>
|
||
* @version 1.0
|
||
* Parser_Ebanka is a parser for getting data from bank account transaction listing
|
||
* in the HTML format used by the Czech bank called "Ebanka" (now Raiffeisen Bank).
|
||
*
|
||
* The parsing is a bit peculiar, because Ebanka uses different format for
|
||
* listings that are visible to general public (the "transparent" listing used
|
||
* by NGOV non-profit organizations) and different format for regular listing used in the
|
||
* ebanking application.
|
||
* This parser autodetects these two formats and parses the data according to it.
|
||
*
|
||
* Benchmarks:
|
||
* Machine: Notebook FSC Lifebook S7110, CPU: Pentium T2400 @ 1.8 GHz
|
||
* Win XP SP2, Apache 2.2.3, PHP 5.2.0
|
||
* Regular listing with 136 table rows (1 week listing): time=0.1 sec, memory=205 kB
|
||
* Regular listing with 2175 table rows (whole year listing): time=1.6 sec, memory=205 kB
|
||
* Transparent listing with 467 table rows: time=0.14 sec, memory=122 kB
|
||
* */
|
||
class Parser_Ebanka extends Parser_Html_Table {
|
||
const START_STRING="Pohyby na"; //Poslední řetězec před začátkem hlavní <TABLE>
|
||
const YEAR_STRING=" za "; //U běžných (netransparentních) výpisů
|
||
const ACCOUNT_STRING="IBAN:"; //
|
||
protected $year=false; //rok výpisu ze záhlaví výpisu
|
||
/**
|
||
* $callback obsahuje jméno funkce, která se má zavolat poté, co naparsujeme
|
||
* 1 kompletní řádek HTML tabulky. Více viz metoda
|
||
* @see set_callback
|
||
* @var string|mixed
|
||
*/
|
||
protected $callback; //callback funkce, kterou můžeme volat např. pro uložení každého řádku výpisu do DB
|
||
/**
|
||
* @var object $result je vkládán do 1. parametru $callback funkce, která přes něj předává jeden řádek výsledku.
|
||
* Pokud $result není nastaven funkcí set_callback, pak jde je nastaven na instanci std_class */
|
||
protected $result;
|
||
/**
|
||
* funkce get_year(..) slouží k tomu, aby z bufferu, který začíná
|
||
* za řetězcem "Za obdob", např. buffer="í 1.12.2007/31.12.2007</td>
|
||
* vytáhla rok výpisu - v tomto příkladě "2007" */
|
||
protected function get_year() {
|
||
while (($dotPos=strpos($this->buffer, "."))===false &&
|
||
$this->get_line()); // hledej první "." v datumu
|
||
if ($dotPos===false) die("Nemůžu najít první znak '.' v řetězci ' za [období] ...'");
|
||
else {
|
||
$toDot=substr($this->buffer, $dotPos+1);
|
||
// hledej 2. tečku zpět od lomítka:
|
||
if (($dotPos2=strpos($toDot, "."))===false)
|
||
die("Nemůžu najít druhý znak '.' v řetězci 'za [období] ...'");
|
||
}
|
||
$this->year=substr($toDot, $dotPos2+1, 4); // získej rok výpisu
|
||
}
|
||
|
||
protected function get_cislo_uctu() {
|
||
while (($czPos=stripos($this->buffer, "CZ"))===false &&
|
||
$this->get_line()); // hledej lomítko
|
||
if ($czPos===false) die("Nemůžu najít 'CZ' v IBAN čísle účtu'");
|
||
else {
|
||
$this->result->parsed_acc_bank_nr=substr($this->buffer, $czPos+4, 4);
|
||
$account_nr=(int)substr($this->buffer, $czPos+8, 16);
|
||
$this->result->parsed_acc_nr="$account_nr";
|
||
}
|
||
}
|
||
|
||
protected function get_amount($field) {
|
||
$field=strip_tags($field);
|
||
$field=str_replace(array(" ", " "), "", $field);
|
||
return strtr($field, ",", ".");
|
||
}
|
||
|
||
/**
|
||
* V posledním sloupci HTML výpisu ebanka dává tyto poplatky: Poplatek, směna, zpráva.
|
||
* Poplatky jsou odděleny značkami <br>, u transp. výpisu <br/>
|
||
* @param $field
|
||
* @param $transparent
|
||
* @return součet všech poplatků generovaných pro daný řádek
|
||
*/
|
||
protected function get_fee($field, $transparent) {
|
||
$field=str_replace(array(" ", " "), "", $field);
|
||
$field=strtr($field, ",", ".");
|
||
if ($transparent) $br_tag="<br/>";
|
||
else $br_tag="<br>";
|
||
$arr=preg_split("/<br>/si", $field);
|
||
$fee=0;
|
||
foreach ($arr as $value)
|
||
$fee+=$value;
|
||
return $fee;
|
||
}
|
||
|
||
|
||
protected function get_data_from_transparent() {
|
||
|
||
$res = $this->result;
|
||
$first=true;
|
||
$line_nr=0;
|
||
do {
|
||
|
||
$status=$this->get_table_rows();
|
||
$nr=count($this->matches[1]);
|
||
$fields=str_replace(array("\r", "\n", "\t"), "", $this->matches[1]);
|
||
/*
|
||
if ($first) {
|
||
$i=6;
|
||
$first=false;
|
||
} else $i=0;
|
||
*/
|
||
for ($i=0; $i<$nr; $i++) {
|
||
$field_nr=$i % 6;
|
||
$field=$fields[$i];
|
||
switch ($field_nr) {
|
||
case 0: // příklad: 31.08.2008<br/>06:1
|
||
$arr=explode("<br/>", $field);
|
||
$arrDate=explode(".", $arr[0]);
|
||
$res->date_time=$arrDate[0];
|
||
//$res->date_time=$arrDate[2]."-".$arrDate[1]."-".$arrDate[0]." ".$arr[1];
|
||
break;
|
||
|
||
case 1: // Poznámky<br/>Název účtu plátce
|
||
$field=html_entity_decode($field,ENT_QUOTES,"UTF-8");
|
||
$arr=explode("<br/>", $field);
|
||
$res->comment=$arr[0];
|
||
$res->name=$arr[1];
|
||
break;
|
||
|
||
case 2: //2x za sebou datum odepsání<br/>typ platby
|
||
$arr=explode("<br/>", $field);
|
||
$res->type=html_entity_decode($arr[2],ENT_QUOTES,"UTF-8");
|
||
break;
|
||
case 3:
|
||
$arr=explode("<br/>", $field); //VS<br/>KS<br/>SS
|
||
$res->variable_symbol=(int)$arr[0];
|
||
$res->constant_symbol=$arr[1];
|
||
$res->specific_symbol=(int)$arr[2];
|
||
break;
|
||
case 4:
|
||
$res->amount=$this->get_amount($field); // částka
|
||
break;
|
Také k dispozici: Unified diff
Pridan Fio importer verze Beta. Co mu chybi: kontrola duplicit, automaticke denni stahovani primo z internetoveho bankovnictvi, neumi rozlisovat zridka se vyskytujici veci jako vklad a vyber (zapocitaji se jako faktury/cl. prispevky) na to potrebuju nejaky pestrejsi vypis, abych vedel, jak to vypada. Pridana podpora mazani vypisu vcetne vsech prevodu i bankovnich prevodu na vypisu. Pri importu uz neni treba vyplnovat udaje z hlavicky, parsuji se automaticky. Celkova restruktulizace importeru.