FreenetIS

Freenetis - open source information system for managing community

based networks. It provides features like payment management, user

and device management, services management etc.

Copyright (C) 2008-2011 Tomas Dulik, Michal Kliment, Jiri Svitak,

Roman Sevcik, Ondrej Fibich, Petr Danek, Marek Rozehnal

This program is free software: you can redistribute it and/or modify

it under the terms of the GNU General Public License as published by

the Free Software Foundation, either version 3 of the License, or

(at your option) any later version.

This program is distributed in the hope that it will be useful,

but WITHOUT ANY WARRANTY; without even the implied warranty of

MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the

GNU General Public License for more details.

You should have received a copy of the GNU General Public License

along with this program. If not, see <http://www.gnu.org/licenses/>.

'all accounts now have correct balances, %d accounts had incorrect balances' => 'Všechny účty teď mají správné zůstatky, %d účtů mělo chybné zůstatky.',

'back to bank statement' => 'Zpět na bankovní výpis',

'balance from date' => 'Zůstatek od data',

'balance to date' => 'Zůstatek do data',

'bank statement contains items that were already imported' => 'Bankovní výpis obsahuje položky, které již byly importovány.',

'entrance fees have been successfully deducted, %d new transfers created' => 'Vstupní příspěvky byly úspěšně strženy, vytvořeno %d nových převodů.',

'entrance fees have been successfully recalculated, %d transfers deleted, %d new transfers created' => 'Vstupní příspěvek byl úspěšně stržen, smazáno %d převodů, vytvořeno %d nových převodů.',

'error - cannot add bank transfer' => 'Chyba - nelze přidat bankovní převod.',

'error - cannot assign transfer' => 'Chyba - nelze přiřadit převod.',

'expenses' => 'Výdaje',

'fees have been successfully deducted, %d new transfers created' => 'Členské příspěvky byly úspěšně strženy, vytvořeno %d nových převodů.',

'fees have been successfully recalculated, %d deleted transfers, %d created new transfers' => 'Členské příspěvky byly úspěšně přepočítány, smazáno %d převodů, vytvořeno %d nových převodů.',

'import has failed' => 'Import selhal.',

'incomes' => 'Příjmy',

'recalculate account balances' => 'Přepočítat zůstatky na účtech',

'statistics' => 'Statistiky',

'summary' => 'Shrnutí',

* @author Jiri Svitak

* @license <http://www.gnu.org/licenses/> GNU/GPLv3

'accounting_system' => 'Účetní osnova je hlavní pomocník pro účetního sdružení. Zobrazuje sumu peněz na účtu z pohledu účetní osnovy. Ve výchozím stavu je to částka za celou dobu fungování sdružení, lze ovšem vyfiltrovat převody za zvolené období, např. od data 2009-01-01 do data 2009-12-31.',

'add_new_bank_transfer' => 'Umožňuje přidat ručně nový bankovní převod, který ma zdrojový i cílový účet. Typicky jde o fakturu nebo členský příspěvek. V běžném provozu využívejte automatický import výpisů. Ruční přidávání je tu pouze z důvodu, že byla potřeba dát do systému staré platby, které již byly jen v textovém formátu a nebylo možné je parsovat.',

'add_new_bank_transfer_without_counteraccount' => 'Umožňuje přidat bankovní převod bez protiúčtu, jde typicky o vklad, úrok bance apod. V běžném provozu využívejte automatický import výpisů. Ruční přidávání je tu pouze z důvodu, že byla potřeba dát do systému staré platby, které již nebylo možné parsovat.',

'recalculate_account_balances' => 'Primárním zdrojem o stavu financí v systému jsou převody. Zůstatky na účtech byly dříve pokaždé počítány na požádání z převodů. Nyní si každý účet ukládá svůj zůstatek kvůli vyšší rychlosti. Tuto funkci použijete v případě přechodu na verzi systému, která tuto optimalizaci obsahuje.',

'recalculate_fees' => 'Pro primární strhávání členských příspěvků používejte funkci pro globální strhávání členských příspěvků v Účetním deníku. Tuto funkci používejte opatrně a to jen v případě, že máte správně strženy všechny členské příspěvky pro všechny členy od založení sdružení až po současné datum. Pak můžete tomuto členovi přepočítat stržení členských příspěvků v případě, že u něj došlo k nutnosti je přepočítat, např. chybné datum vstupu, vznikla nutnost přidat přerušení členství apod.',

'recalculate_entrance_fees' => 'Pro primární strhávání vstupních příspěvků používejte funkci pro globální strhávání v účetním deníku. Tuto funkci používejte jen v případě, že došlo k nějakému omylu, například, že měl zaplatit vstupní a pak se ukázalo, že nemusel.',

bt.id AS bt_id, bt.variable_symbol, bt.constant_symbol, bt.specific_symbol, bt.bank_statement_id

a.balance,

' AND account_attribute_id <> '.Account_attribute_Model::$credit;

// default datetime of transfers

$datetime_from_t1 = " AND t1.datetime >= '0000-00-00'";

$datetime_to_t1 = " AND t1.datetime <= '9999-12-31'";

$datetime_from_t2 = " AND t2.datetime >= '0000-00-00'";

$datetime_to_t2 = " AND t2.datetime <= '9999-12-31'";

if ($key == 'name')

$where .= "a.name LIKE '%".$value."%' COLLATE utf8_general_ci";

if ($key == 'datetime_from')

$datetime_from_t1 = " AND t1.datetime >= '$value'";

$datetime_from_t2 = " AND t2.datetime >= '$value'";

if ($key == 'datetime_to')

{

$datetime_to_t1 = " AND t1.datetime <= '$value'";

$datetime_to_t2 = " AND t2.datetime <= '$value'";

}

LEFT JOIN transfers t1 ON a.id = t1.origin_id $datetime_from_t1 $datetime_to_t1

LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime_from_t2 $datetime_to_t2

if ($key == 'name')

$where .= "a.name LIKE '%".$value."%' COLLATE utf8_general_ci";

SELECT a.id, a.balance, m.entrance_date, m.leaving_date,

mf.name AS fee_name,

IF(t.id IS NULL, 0, t.id) AS transfer_id

LEFT JOIN transfers t ON t.origin_id = a.id AND t.type = ".Transfer_Model::$deduct_member_fee." AND t.datetime = '$date'

function get_account_to_recalculate_entrance_fees($account_id)

SELECT a.id, m.entrance_fee, m.debt_payment_rate, m.entrance_date

* Function deletes member fee deducting transfers of given account.

* @author Jiri Svitak

* @param $account_id

* @return unknown_type

*/

public function delete_deduct_transfers_of_account($account_id)

{

$transfers = ORM::factory('transfer')->where(array('type' => Transfer_Model::$deduct_member_fee,

'origin_id' => $account_id))->find_all();

$transfers_count = 0;

foreach ($transfers as $transfer)

{

$transfers_count++;

if (!$transfer->delete())

throw new ErrorException();

}

// recalculate balance of current credit account

if (!$this->recalculate_account_balance_of_account($account_id))

throw new ErrorException();

// recalculate balance of operating account

$operating = ORM::factory('account')->where(array('account_attribute_id' => Account_attribute_Model::$operating))->find();

if (!$this->recalculate_account_balance_of_account($operating->id))

throw new ErrorException();

return $transfers_count;

}

/**

$transfers = ORM::factory('transfer')->where(array('type' => Transfer_Model::$deduct_entrance_fee,

'origin_id' => $account_id))->find_all();

$transfers_count = 0;

foreach ($transfers as $transfer)

{

$transfers_count++;

if (!$transfer->delete())

throw new ErrorException();

}

// recalculate balance of current credit account

if (!$this->recalculate_account_balance_of_account($account_id))

throw new ErrorException();

// recalculate balance of operating account

$operating = ORM::factory('account')->where(array('account_attribute_id' => Account_attribute_Model::$infrastructure))->find();

if (!$this->recalculate_account_balance_of_account($operating->id))

throw new ErrorException();

return $transfers_count;

/**

* Recalculates all account balances.

* @author Jiri Svitak

* @return int count of accounts with corrected balances

*/

public function recalculate_account_balances()

{

$accounts = $this->db->query("

SELECT q2.*, (inbound - outbound) AS calculated_balance

FROM

(

SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound

FROM

(

SELECT a.*, IFNULL(SUM(amount), 0) AS outbound

FROM accounts a

LEFT JOIN members m ON m.id = a.member_id

LEFT JOIN transfers t1 ON a.id = t1.origin_id

GROUP BY a.id

) q1

LEFT JOIN transfers t2 ON q1.id = t2.destination_id

GROUP BY q1.id

) q2

");

// create update sql query

$sql = "UPDATE accounts SET balance = CASE id ";

// incorrect balances

$incorrect_balances = 0;

// array of ids to change

$ids = array();

foreach ($accounts as $account)

{

if ($account->balance != $account->calculated_balance)

{

$incorrect_balances++;

$sql .= "WHEN $account->id THEN $account->calculated_balance ";

$ids[] = $account->id;

}

}

// are there some accounts with incorrect balances? save correct balances

if ($incorrect_balances > 0)

{

$ids_with_commas = implode(',', $ids);

$sql .= "END WHERE id IN ($ids_with_commas)";

$db = new Database();

$db->query($sql);

}

return $incorrect_balances;

}

/**

* Recalculates account balance of single account.

* @author Jiri Svitak

* @param <type> $account_id

*/

public function recalculate_account_balance_of_account($account_id)

{

$account_model = new Account_Model();

$balance = $account_model->get_account_balance($account_id);

return $this->db->query("UPDATE accounts SET balance = '$balance' WHERE id = $account_id");

}

$datetime_from_t1 = " AND t1.datetime >= '0000-00-00'";

$datetime_to_t1 = " AND t1.datetime <= '9999-12-31'";

$datetime_from_t2 = " AND t2.datetime >= '0000-00-00'";

$datetime_to_t2 = " AND t2.datetime <= '9999-12-31'";

if ($key == 'datetime_from')

$datetime_from_t1 = " AND t1.datetime >= '$value'";

$datetime_from_t2 = " AND t2.datetime >= '$value'";

if ($key == 'datetime_to')

{

$datetime_to_t1 = " AND t1.datetime <= '$value'";

$datetime_to_t2 = " AND t2.datetime <= '$value'";

}

LEFT JOIN transfers t1 ON q0.id = t1.origin_id $datetime_from_t1 $datetime_to_t1

LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime_from_t2 $datetime_to_t2

<?php

/**

* @author Jiri Svitak

* @param <type> $origin_id origin account id

* @param <type> $destination_id destination account id

* @param <type> $previous_transfer_id previous transfer id, useful for transfer groups

* @param <type> $member_id transaction owner id

* @param <type> $user_id id of user who added transfer

* @param <type> $type type of transfer, see Transfer_Model

* @param <type> $datetime accounting datetime of transfer

* @param <type> $creation_datetime datetime of transfer creation

* @param <type> $text transfer text

* @param <type> $amount amount of transfer

* @throws ErrorException when failed transfer insert, origin or destination account update

*/

public static function insert_transfer($origin_id, $destination_id, $previous_transfer_id,

$member_id, $user_id, $type, $datetime, $creation_datetime, $text, $amount)

{

// insert new transfer

$transfer = new Transfer_Model();

$transfer->origin_id = $origin_id;

$transfer->destination_id = $destination_id;

$transfer->previous_transfer_id = $previous_transfer_id;

$transfer->member_id = $member_id;

$transfer->user_id = $user_id;

$transfer->type = $type;

$transfer->datetime = $datetime;

$transfer->creation_datetime = $creation_datetime;

$transfer->text = $text;

$transfer->amount = $amount;

if (!$transfer->save())

throw new ErrorException();

// update balance of origin account

$oa = new Account_Model($origin_id);

$oa->balance -= $amount;

if (!$oa->save())

throw new ErrorException();

// update balance of destination account

$da = new Account_Model($destination_id);

$da->balance += $amount;

if (!$da->save())

throw new ErrorException();

return $transfer->id;

}

/**

* Edits transfers safely with change of dependent account balance.

* @author Jiri Svitak

* @param <type> $id

* @param <type> $text

* @param <type> $amount

*/

public static function edit_transfer($id, $text, $amount)

{

// update transfer

$transfer = new Transfer_Model($id);

$transfer->text = $text;

$transfer->amount = $amount;

if (!$transfer->save())

throw new ErrorException();

// update balance of origin account

$oa = new Account_Model($transfer->origin_id);

$oa->balance -= $amount;

if (!$oa->save())

throw new ErrorException();

// update balance of destination account

$da = new Account_Model($transfer->destination_id);

$da->balance += $amount;

if (!$da->save())

throw new ErrorException();

}

/**

* Safely deletes transfer.

* @author Jiri Svitak

* @param <type> $id

*/

public static function delete_transfer($id)

{

$transfer = new Transfer_Model($id);

// update balance of origin account

$oa = new Account_Model($transfer->origin_id);

$oa->balance += $transfer->amount;

if (!$oa->save())

throw new ErrorException();

// update balance of destination account

$da = new Account_Model($transfer->destination_id);

$da->balance -= $transfer->amount;

if (!$da->save())

throw new ErrorException();

// delete transfer

if (!$transfer->delete())

throw new ErrorException();

}

* @license <http://www.gnu.org/licenses/> GNU/GPLv3

// inbound and outbound amount of money are calculated from transfers of account

// balance is not calculated, fast redundant value from account itself is used

$balance = $account->balance;

$grid->add_new_button(url_lang::base().'transfers/recalculate_fees/'.$account->id, url_lang::lang('texts.Recount of member fees'), array('onclick' => 'return potvrd(\''.url_lang::lang('texts.Are you sure you want to recount fees of this member').'\')'), help::hint('recalculate_fees'));

$grid->add_new_button(url_lang::base().'transfers/recalculate_entrance_fees/'.$account->id, url_lang::lang('texts.Recount of entrance fees'), array('onclick' => 'return potvrd(\''.url_lang::lang('texts.Are you sure you want to recount entrance fees of this member').'\')'), help::hint('recalculate_entrance_fees'));

try

$db = new Transfer_Model();

$db->transaction_start();

Transfer_Model::insert_transfer($form_data['oname'], $form_data['aname'], null,

null, $this->session->get('user_id'), null, date('Y-m-d', $form_data['datetime']),

date('Y-m-d H:i:s'), $form_data['text'], $form_data['amount']);

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Transfer has been successfully added.'));

catch (ErrorException $e)

{

$db->transaction_rollback();

$this->session->set_flash('message', url_lang::lang('texts.Error - cannot add new transfer.'));

}

url::redirect(url_lang::base().'transfers/show_by_account/'.$origin_account_id);

try

$db = new Transfer_Model();

$db->transaction_start();

Transfer_Model::insert_transfer($form_data['oname'], $form_data['aname'], null,

null, $this->session->get('user_id'), null, date('Y-m-d', $form_data['datetime']),

date('Y-m-d H:i:s'), $form_data['text'], $form_data['amount']);

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Transfer has been successfully added.'));

}

catch (ErrorException $e)

{

$db->transaction_rollback();

url::redirect(url_lang::base().'transfers/show_all');

try

$db = new Transfer_Model();

$db->transaction_start();

Transfer_Model::insert_transfer($form_data['oname'], $operating->id, null,

null, $this->session->get('user_id'), Transfer_Model::$deduct_voip_unnaccounted_fee,

date('Y-m-d', $form_data['datetime']),

date('Y-m-d H:i:s'), $text, $form_data['amount']);

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Transfer has been successfully added.'));

}

catch (ErrorException $e)

{

$db->transaction_rollback();

url::redirect(url_lang::base().'transfers/show_by_account/'.$origin_account);

try

{

$db = new Transfer_Model();

$db->transaction_start();

Transfer_Model::edit_transfer($transfer_id, htmlspecialchars($form_data['text']), htmlspecialchars($form_data['amount']));

$db->transaction_commit();

url::redirect(url_lang::base().'transfers/show/'.$transfer_id);

}

catch (ErrorException $e)

{

$db->transaction_rollback();

url::redirect(url_lang::base().'transfers/show/'.$transfer_id);

}

$links[] = html::anchor(url_lang::base().'transfers/show_by_account/'.$ssAccount_id, url_lang::lang('texts.Back to transfers of account'));

$view->content->link_back = implode (' | ', $links);

* Deducts fees of all members in one month. If deduct transfer for one month and

* account is found, then it is ignored and skipped.

// preparation

$created_transfers_count = 0;

$total_amount = 0;

$user_id = $this->session->get('user_id');

{

}

$this->session->set_flash('message', url_lang::lang('texts.Fees have not been set!'));

$database = new Database();

try

$db = new Transfer_Model();

$db->transaction_start();

// first sql for inserting transfers

$sql_insert = "INSERT INTO transfers (origin_id, destination_id, previous_transfer_id, member_id, user_id, type, datetime, creation_datetime, text, amount) VALUES ";

$values = array();

// second sql for updating accounts

$sql_update = "UPDATE accounts SET balance = CASE id ";

$ids = array();

// main cycle

foreach ($accounts as $account)

// no deduct transfer for this date and account generated? then create one

if ($account->transfer_id == 0)

{

$text = url_lang::lang('texts.Deduction of member fee');

if ($account->fee_is_set)

{

$amount = $account->fee;

$text = ($account->fee_readonly) ? $text.' - '.url_lang::lang('texts.'.$account->fee_name) : $text.' - '.$account->fee_name;

}

else

{

$amount = $default_fee;

}

// is amount bigger than zero?

if ($amount > 0)

{

// insert

$values[] = "($account->id, $operating->id, NULL, NULL, $user_id, ".Transfer_Model::$deduct_member_fee.", '$date', '$creation_datetime', '$text', $amount)";

// update

$sql_update .= "WHEN $account->id THEN ".($account->balance - $amount)." ";

$ids[] = $account->id;

$total_amount += $amount;

$created_transfers_count++;

}

}

if ($created_transfers_count > 0)

// single query for inserting transfers

$sql_insert .= implode(",", $values);

if (!$database->query($sql_insert))

throw new ErrorException();

// single query for updating credit account balances

$ids_with_commas = implode(",", $ids);

$sql_update .= "END WHERE id IN ($ids_with_commas)";

if (!$database->query($sql_update))

throw new ErrorException();

// update also balance of operating account

if (!$account_model->recalculate_account_balance_of_account($operating->id))

throw new ErrorException();

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Fees have been successfully deducted, %d new transfers created.', $created_transfers_count));

catch(ErrorException $e)

$db->transaction_rollback();

* Function recalculates wrong deducted fees of given account of member.

function recalculate_fees($account_id = 0)

if ($day > 15)

{

}

// find datetime of last deduct fee

$transfer_model = new Transfer_Model();

$last_datetime = $transfer_model->find_last_transfer_datetime_by_type(Transfer_Model::$deduct_member_fee);

if ($max_day > 15)

try

$db = new Transfer_Model();

$db->transaction_start();

// deleting old deduct transfer of member

$deleted_transfers_count = $account->delete_deduct_transfers_of_account($account_id);

$created_transfers_count = 0;

$max_date = date('Y-m-d', mktime(0, 0, 0, $max_month, 15, $max_year));

while (($date = date('Y-m-d', mktime(0, 0, 0, $month, 15, $year))) <= $max_date)

$text = url_lang::lang('texts.Deduction of member fee');

// finds default regular member fee for this month

$fee = $fee_model->get_default_fee_by_date_type($date, 'regular member fee');

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

$default_fee = $fee->fee;

else

throw new ErrorException(url_lang::lang('texts.Fatal error'), url_lang::lang('texts.Fees have not been set!'));

// finds regular member fee for this member and this month

$fee = $fee_model->get_fee_by_member_date_type($account->member_id, $date, 'regular member fee');

// it exists

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

{

$amount = $fee->fee;

// translate only read-only fee names

$text = ($fee->readonly) ? $text.' - '.url_lang::lang('texts.'.$fee->name) : $text.' - '.$fee->name;

}

// it doesn't exist - uses default fee

else

{

$amount = $default_fee;

}

if ($amount > 0)

{

$created_transfers_count++;

Transfer_Model::insert_transfer($account->id, $operating->id, null, null,

$this->session->get('user_id'), Transfer_Model::$deduct_member_fee,

$date, $creation_datetime, $text, $amount);

}

// iterate to next month

$month++;

if ($month == 13)

{

$month = 1;

$year++;

}

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Fees have been successfully recalculated, %d deleted transfers, %d created new transfers.', array(0 => $deleted_transfers_count, 1=> $created_transfers_count)));

catch (ErrorException $e)

{

$db->transaction_rollback();

}

$created_transfers_count = 0;

try

$db = new Transfer_Model();

$db->transaction_start();

foreach($credit_accounts as $ca)

// if member's entrance fee is 0, then no transfer is generated

if ($ca->entrance_fee == 0)

continue;

// are there some deduct transfers?

if (empty($ca->transfer_id))

// no deduct entrance fee transfer means that nothing is already paid

$already_paid = 0;

$latest_date = '0001-01-01';

// some transfers already exist, then their total amount is calculated

$eftransfers = $transfer_model->get_entrance_fee_transfers_of_account($ca->id);

$already_paid = $eftransfers->current()->total_amount;

$latest_date = $eftransfers->current()->datetime;

$latest_date = substr($latest_date, 0, strpos($latest_date, ' '));

// is already whole entrance fee paid?

if ($already_paid >= $ca->entrance_fee)

continue;

// entrance fee is not wholy paid, calculate debt

$debt = $ca->entrance_fee - $already_paid;

// entrance date of current member

$entrance = date_parse($ca->entrance_date);

$eyear = $entrance['year'];

$emonth = $entrance['month'];

$eday = $entrance['day'];

if ($eday > 15)

$emonth++;

$eyear++;

$date = date('Y-m-d', mktime(0, 0, 0, $emonth, 15, $eyear));

// while debt is greater than zero and date of instalment is lower than current date

while ($debt > 0 && $date < $current_date)

{

// is debt still greater than one monthly instalment?

if ($debt > $ca->debt_payment_rate)

{

// one monthly instalment is deducted

$amount = $ca->debt_payment_rate;

// if debt pay rate is zero, then whole amount of debt is deducted

if ($amount <= 0)

$amount = $debt;

}

else

{

// rest of the debt is deducted

$amount = $debt;

}

// only one transfer per month - older transfers cannot be duplicated

if ($date > $latest_date)

{

// decrease amount of debt due to size of one instalment

$debt -= $amount;

// create new transfer

Transfer_Model::insert_transfer($ca->id, $infrastructure->id, null, null,

$this->session->get('user_id'), Transfer_Model::$deduct_entrance_fee,

$date, $creation_datetime, url_lang::lang('texts.Entrance fee'), $amount);

$created_transfers_count++;

}

// iterate to next month

$emonth++;

if ($emonth == 13)

{

$emonth = 1;

$eyear++;

}

// getting boundary date - 15th day in the month

$date = date('Y-m-d', mktime(0, 0, 0, $emonth, 15, $eyear));

}

$db->transaction_commit();

$this->session->set_flash('message', url_lang::lang('texts.Entrance fees have been successfully deducted, %d new transfers created.', $created_transfers_count));

catch(ErrorException $e)

$db->transaction_rollback();

* Recounts transfer of one member credit account.

* Used only in special cases, like changing entrance date.

function recalculate_entrance_fees($account_id)