Revize 674
Přidáno uživatelem Jiří Sviták před více než 14 roky(ů)
| freenetis/trunk/kohana/application/models/account.php | ||
|---|---|---|
|
* @author Jiri Svitak
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function get_accounts($limit_from = 0, $limit_results = 20, $order_by = 'id', $order_by_direction = 'asc', $filter_values = array())
|
||
|
public function get_accounts($limit_from = 0, $limit_results = 20, $order_by = 'id', $order_by_direction = 'asc', $filter_values = array(), $group = 4)
|
||
|
{
|
||
|
$where = '';
|
||
|
$where = 'WHERE ';
|
||
|
// group - project, credit and other accounts
|
||
|
if ($group == self::$project)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$project;
|
||
|
else if ($group == self::$credit)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$credit;
|
||
|
else
|
||
|
$where .= 'account_attribute_id <> '.Account_attribute_Model::$project.
|
||
|
' AND account_attribute_id <> '.Account_attribute_Model::$credit;
|
||
|
|
||
|
$datetime1 = '';
|
||
|
$datetime2 = '';
|
||
|
foreach ($filter_values as $key => $value)
|
||
|
{
|
||
|
if ($key != 'submit' && $key != 'datetime')
|
||
|
{
|
||
|
if ($where == '')
|
||
|
$where = 'WHERE ';
|
||
|
else
|
||
|
$where .= ' AND ';
|
||
|
$where .= ' AND ';
|
||
|
if ($key == 'name')
|
||
|
$where .= "a.name LIKE '%".$value."%' COLLATE utf8_general_ci";
|
||
|
if ($key == 'group')
|
||
|
{
|
||
|
if ($value == self::$project)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$project;
|
||
|
else if ($value == self::$credit)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$credit;
|
||
|
else
|
||
|
$where .= 'account_attribute_id <> '.Account_attribute_Model::$project.
|
||
|
' AND account_attribute_id <> '.Account_attribute_Model::$credit;
|
||
|
}
|
||
|
}
|
||
|
if ($key == 'datetime')
|
||
|
{
|
||
| ... | ... | |
|
}
|
||
|
// query
|
||
|
return self::$db->query("
|
||
|
SELECT q2.*, (inbound - outbound) AS balance FROM
|
||
|
(SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound FROM
|
||
|
(SELECT a.id, a.name, a.account_attribute_id,
|
||
|
CONCAT('<a href=\"".url_lang::base()."members/show/', a.member_id, '\">', m.name, '</a>') AS mname,
|
||
|
SELECT q2.*, (inbound - outbound) AS balance
|
||
|
FROM
|
||
|
(
|
||
|
SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound
|
||
|
FROM
|
||
|
(
|
||
|
SELECT a.id, a.name, a.account_attribute_id,
|
||
|
m.name AS member_name, m.id AS member_id,
|
||
|
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 $datetime1
|
||
|
$where
|
||
|
GROUP BY a.id
|
||
|
) q1
|
||
|
) q1
|
||
|
LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime2
|
||
|
$where
|
||
|
GROUP BY q1.id
|
||
|
) q2
|
||
|
) q2
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT $limit_from, $limit_results"
|
||
|
);
|
||
| ... | ... | |
|
* @author Jiri Svitak
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
public function get_accounts_count($filter_values = array())
|
||
|
public function get_accounts_count($filter_values = array(), $group = 4)
|
||
|
{
|
||
|
$where = '';
|
||
|
$where = 'WHERE ';
|
||
|
// group - project, credit and other accounts
|
||
|
if ($group == self::$project)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$project;
|
||
|
else if ($group == self::$credit)
|
||
|
$where .= 'account_attribute_id = '.Account_attribute_Model::$credit;
|
||
|
else
|
||
|
$where .= 'account_attribute_id <> '.Account_attribute_Model::$project.
|
||
|
' AND account_attribute_id <> '.Account_attribute_Model::$credit;
|
||
|
foreach ($filter_values as $key => $value)
|
||
|
{
|
||
|
if ($key != 'submit' && $key != 'datetime')
|
||
|
{
|
||
|
if ($where == '')
|
||
|
$where = 'WHERE ';
|
||
|
else
|
||
|
$where .= ' AND ';
|
||
|
$where .= ' AND ';
|
||
|
if ($key == 'name')
|
||
|
$where .= "a.name LIKE '%".$value."%' COLLATE utf8_general_ci";
|
||
|
if ($key == 'group')
|
||
|
{
|
||
|
if ($value == self::$project)
|
||
|
$where .= 'a.account_attribute_id = '.Account_attribute_Model::$project;
|
||
|
else if ($value == self::$credit)
|
||
|
$where .= 'a.account_attribute_id = '.Account_attribute_Model::$credit;
|
||
|
else
|
||
|
$where .= 'a.account_attribute_id <> '.Account_attribute_Model::$project.
|
||
|
' AND a.account_attribute_id <> '.Account_attribute_Model::$credit;
|
||
|
}
|
||
|
}
|
||
|
}
|
||
|
return self::$db->query("SELECT COUNT(*) AS total
|
||
|
}
|
||
|
return self::$db->query("
|
||
|
SELECT COUNT(*) AS total
|
||
|
FROM accounts a
|
||
|
$where"
|
||
|
)->current()->total;
|
||
|
}
|
||
|
|
||
|
|
||
|
/**
|
||
|
* Function gets some double-entry accounts. Used in dropdown to select destination account.
|
||
|
* @return unknown_type
|
||
| freenetis/trunk/kohana/application/models/transfer.php | ||
|---|---|---|
|
if ($key == 'text')
|
||
|
$where .= "t.text LIKE '%".$value."%' COLLATE utf8_general_ci";
|
||
|
if ($key == 'datetime')
|
||
|
$where .= "t.datetime LIKE '%".$value."%' COLLATE utf8_general_ci";
|
||
|
$where .= "t.datetime LIKE '%".$value."%'";
|
||
|
if ($key == 'amount')
|
||
|
$where .= "t.amount LIKE '%".$value."%' COLLATE utf8_general_ci";
|
||
|
}
|
||
| ... | ... | |
|
IF(t.amount <> 0, IF(t.destination_id = $account_id, t.amount, -t.amount), 0) AS amount,
|
||
|
t.datetime,
|
||
|
a.name,
|
||
|
IF(t.destination_id = $account_id, bt.variable_symbol, NULL) AS variable_symbol
|
||
|
IF(t.destination_id = $account_id, bt.variable_symbol, NULL) AS variable_symbol,
|
||
|
(
|
||
|
SELECT SUM(IF(t.destination_id = $account_id, t.amount, 0)) AS inbound
|
||
|
FROM transfers t
|
||
|
LEFT JOIN accounts a ON a.id = IF(t.origin_id = $account_id, t.destination_id, t.origin_id)
|
||
|
$where
|
||
|
) AS inbound,
|
||
|
(
|
||
|
SELECT SUM(IF(t.origin_id = $account_id, t.amount, 0)) AS outbound
|
||
|
FROM transfers t
|
||
|
LEFT JOIN accounts a ON a.id = IF(t.origin_id = $account_id, t.destination_id, t.origin_id)
|
||
|
$where
|
||
|
) AS outbound
|
||
|
FROM transfers t
|
||
|
LEFT JOIN accounts a ON a.id = IF(t.origin_id = $account_id, t.destination_id, t.origin_id)
|
||
|
LEFT JOIN transfers pt ON pt.id = t.previous_transfer_id
|
||
|
LEFT JOIN bank_transfers bt ON pt.id = bt.transfer_id
|
||
|
$where
|
||
|
GROUP BY t.id
|
||
|
ORDER BY $order_by $order_by_direction
|
||
|
LIMIT $limit_from, $limit_results"
|
||
|
);
|
||
|
}
|
||
|
/*
|
||
|
,
|
||
|
SUM(IF(t.destination_id = $account_id, t.amount, 0)) AS inbound,
|
||
|
SUM(IF(t.origin_id = $account_id, t.amount, 0)) AS outbound
|
||
|
*/
|
||
|
|
||
|
/**
|
||
|
* Function gets all money transfers of double-entry account.
|
||
| freenetis/trunk/kohana/application/controllers/transfers.php | ||
|---|---|---|
|
if (($sql_offset = ($page - 1) * $limit_results) > $total_transfers)
|
||
|
$sql_offset = 0;
|
||
|
$transfers = $transfer_model->get_transfers($account_id, $sql_offset, (int)$limit_results, $order_by, $order_by_direction, $filter_values);
|
||
|
// total amount of inbound and outbound transfers
|
||
|
$inbound = $outbound = 0;
|
||
|
if (count($transfers) > 0)
|
||
|
{
|
||
|
$inbound = $transfers->current()->inbound;
|
||
|
$outbound = $transfers->current()->outbound;
|
||
|
}
|
||
|
// headline
|
||
|
$headline = url_lang::lang('texts.Transfers of double-entry account');
|
||
|
// grid of transfers
|
||
| ... | ... | |
|
$view->content->headline = $headline;
|
||
|
$view->content->account = $account;
|
||
|
$view->content->balance = $balance;
|
||
|
$view->content->inbound = $account->get_total_inbound_amount($account_id);
|
||
|
$view->content->outbound = $account->get_total_outbound_amount($account_id);
|
||
|
$view->content->inbound = $inbound;
|
||
|
$view->content->outbound = $outbound;
|
||
|
$view->content->expiration_date = $expiration_date;
|
||
|
$view->content->grid = $grid;
|
||
|
$view->content->message = $this->session->get_once('message');
|
||
| freenetis/trunk/kohana/application/controllers/accounts.php | ||
|---|---|---|
|
{
|
||
|
function index()
|
||
|
{
|
||
|
url::redirect(url_lang::base().'accounts/show_all');
|
||
|
url::redirect(url_lang::base().'accounts/show_all/1');
|
||
|
}
|
||
|
|
||
|
/**
|
||
| ... | ... | |
|
* @param $order_by_direction
|
||
|
* @return unknown_type
|
||
|
*/
|
||
|
function show_all($limit_results = 500, $order_by = 'id', $order_by_direction = 'asc', $page_word = null, $page = 1)
|
||
|
function show_all($group = 1, $limit_results = 500, $order_by = 'id', $order_by_direction = 'asc', $page_word = null, $page = 1)
|
||
|
{
|
||
|
if(!$this->acl_check_view('Accounts_Controller','accounts'))
|
||
|
Controller::error(ACCESS);
|
||
|
$this->session->del('ssMember_id');
|
||
|
$this->session->del('ssAccount_id');
|
||
|
// account groups
|
||
|
$arr_groups[Account_Model::$accounting_system] = url_lang::lang('texts.Accounting system');
|
||
|
$arr_groups[Account_Model::$credit] = url_lang::lang('texts.Credit subaccounts');
|
||
|
$arr_groups[Account_Model::$project] = url_lang::lang('texts.Project subaccounts');
|
||
|
$arr_groups[Account_Model::$other] = url_lang::lang('texts.Other');
|
||
|
// filtering
|
||
|
$filter=new Table_Form(url_lang::base()."accounts/show_all", "get", array(
|
||
|
new Table_Form_Item('text', 'name', 'Account name'),
|
||
|
new Table_Form_Item('select', 'group', 'Group', $arr_groups),
|
||
|
//new Table_Form_Item('select', 'group', 'Group', $arr_groups),
|
||
|
"tr",
|
||
|
new Table_Form_Item('text', 'datetime', 'Balance to year'),
|
||
|
"tr",
|
||
|
"td", new Table_Form_Item('submit', 'submit', 'Filter'),
|
||
|
)
|
||
|
);
|
||
| ... | ... | |
|
$order_by_direction = 'asc';
|
||
|
|
||
|
// choosing database model
|
||
|
if (isset($filter_values['group']))
|
||
|
/*if (isset($filter_values['group']))
|
||
|
$load_account_system = $filter_values['group'] == Account_Model::$accounting_system;
|
||
|
else
|
||
|
$load_account_system = true;
|
||
|
if ($load_account_system)
|
||
|
*/
|
||
|
//if ($load_account_system)
|
||
|
if ($group == Account_Model::$accounting_system)
|
||
|
{
|
||
|
$account_attribute_model = new Account_attribute_Model();
|
||
|
$total_accounts = $account_attribute_model->get_accounting_system_count($filter_values);
|
||
| ... | ... | |
|
else
|
||
|
{
|
||
|
$account_model = new Account_Model();
|
||
|
$total_accounts = $account_model->get_accounts_count($filter_values);
|
||
|
$total_accounts = $account_model->get_accounts_count($filter_values, $group);
|
||
|
if (($sql_offset = ($page - 1) * $limit_results) > $total_accounts)
|
||
|
$sql_offset = 0;
|
||
|
$accounts = $account_model->get_accounts($sql_offset, (int)$limit_results, $order_by, $order_by_direction, $filter_values);
|
||
|
$accounts = $account_model->get_accounts($sql_offset, (int)$limit_results, $order_by, $order_by_direction, $filter_values, $group);
|
||
|
}
|
||
|
|
||
|
// creates parameters of filter in url
|
||
| ... | ... | |
|
}
|
||
|
$query_string = '?'.implode('&', $arr_gets);
|
||
|
|
||
|
if (count($filter_values) == 0)
|
||
|
$filter_values['group'] = Account_Model::$accounting_system;
|
||
|
//if (count($filter_values) == 0)
|
||
|
// $filter_values['group'] = Account_Model::$accounting_system;
|
||
|
// set correct headline for chosen group
|
||
|
$headline = $arr_groups[$filter_values['group']];
|
||
|
// save current group of double entry accounts to session
|
||
|
$this->session->set('account_group', $filter_values['group']);
|
||
|
$headline = $arr_groups[$group];
|
||
|
//$this->session->set('account_group', $filter_values['group']);
|
||
|
|
||
|
$grid = new Grid(url_lang::base().'accounts', null, array(
|
||
|
//'separator' => '<br /><br />',
|
||
| ... | ... | |
|
'query_string' => $query_string,
|
||
|
'filter' => $filter->view
|
||
|
));
|
||
|
// adding project account
|
||
|
if ($filter_values['group'] == Account_Model::$project && $this->acl_check_new('Accounts_Controller', 'accounts'))
|
||
|
$grid->add_new_button(url_lang::base().'accounts/add_project', url_lang::lang('texts.Add new project account'));
|
||
|
if ($load_account_system)
|
||
|
foreach($arr_groups as $key=>$arr_group)
|
||
|
$grid->add_new_button(url_lang::base().'accounts/show_all/'.$key, $arr_group);
|
||
|
if ($group == Account_Model::$accounting_system)
|
||
|
{
|
||
|
$grid->order_field('id')->label(url_lang::lang('texts.Account'));
|
||
|
$grid->order_field('name')->label(url_lang::lang('texts.Account name'));
|
||
| ... | ... | |
|
}
|
||
|
else
|
||
|
{
|
||
|
// adding project account
|
||
|
if ($group == Account_Model::$project && $this->acl_check_new('Accounts_Controller', 'accounts'))
|
||
|
$grid->add_new_button(url_lang::base().'accounts/add_project', url_lang::lang('texts.Add new project account'));
|
||
|
$grid->order_field('id')->label('ID');
|
||
|
$grid->order_field('name')->label(url_lang::lang('texts.Account name'));
|
||
|
$grid->order_field('account_attribute_id')->label(url_lang::lang('texts.Type'));
|
||
| ... | ... | |
|
$grid->action_field('id')->label(url_lang::lang('texts.Transfers'))->url(url_lang::base().'transfers/show_by_account')->action(url_lang::lang('texts.Show'));
|
||
|
if ($this->acl_check_edit('Accounts_Controller', 'accounts'))
|
||
|
$grid->action_field('id')->label(url_lang::lang('texts.Account'))->url(url_lang::base().'accounts/edit')->action(url_lang::lang('texts.Edit'));
|
||
|
$grid->field('mname')->label(url_lang::lang('texts.Member name'));
|
||
|
$grid->order_callback_field('member_name')->label(url_lang::lang('texts.Member name'))->callback('Members_Controller::member_field');
|
||
|
}
|
||
|
$grid->datasource($accounts);
|
||
|
|
||
| ... | ... | |
|
$form->textarea('comment')->label(url_lang::lang('texts.Comment').':')->rules('length[0,250]')->value($model_account->comment);
|
||
|
$form->submit('submit')->value(url_lang::lang('texts.Edit'));
|
||
|
special::required_forge_style($form, ' *', 'required');
|
||
|
$linkback = url_lang::base()."accounts/show_all?name=&group=".$this->session->get('account_group')."&submit=Filter";
|
||
|
// variant with session
|
||
|
//$linkback = url_lang::base()."accounts/show_all?name=&group=".$this->session->get('account_group')."&submit=Filter";
|
||
|
$linkback = url_lang::base()."accounts/show_all/1";
|
||
|
if($form->validate())
|
||
|
{
|
||
|
$form_data = $form->as_array();
|
||
Také k dispozici: Unified diff
Upravy podvojnych uctu a rozdelany vypocty sum.