|
<?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|
* This file is part of open source system FreeNetIS
|
|
* and it is release 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/
|
|
*
|
|
*/
|
|
|
|
/**
|
|
* @package Model
|
|
*/
|
|
class Account_attribute_Model extends ORM
|
|
{
|
|
protected $has_one = array('account');
|
|
|
|
/**
|
|
* definition of double entry accounts numbers used in various Freenetis controllers and models.
|
|
* These numbers are also valid values of the account_attribute primary key!
|
|
*
|
|
*/
|
|
public static $cash = '211000'; // created in installation
|
|
public static $bank = '221000'; // created in installation
|
|
public static $credit = '221100';
|
|
public static $operating = '221101'; // created in installation
|
|
public static $infrastructure = '221102'; // created in installation
|
|
public static $project = '221103';
|
|
public static $bank_debts = '231000';
|
|
public static $purchasers = '311000'; // created in installation
|
|
public static $suppliers = '321000'; // created in installation
|
|
public static $bank_fees = '549001'; // created in installation
|
|
public static $bank_interests = '644000'; // created in installation
|
|
public static $member_fees = '684000'; // created in installation
|
|
public static $time_deposits_interests = '655000';
|
|
public static $time_deposits = '259000';
|
|
|
|
/**
|
|
* The constructor is used for filling the double accounts numbers from the database.
|
|
* @todo: the public static constants listed above should be filled from the
|
|
* "config" database table, using the name of the constant.
|
|
* If a constant is not found in the table, its predefined value would be kept.
|
|
*/
|
|
//public function __construct() {}
|
|
|
|
/**
|
|
* Accounting system requires special query.
|
|
* For every account type it counts balance. For example credit accounts 221100 is one account
|
|
* representing money on all credit accounts in system.
|
|
*
|
|
* @return unknown_type
|
|
*/
|
|
public function get_accounting_system($limit_from = 0, $limit_results = 20,
|
|
$order_by = 'id', $order_by_direction = 'asc', $filter_values = array())
|
|
{
|
|
$where = '';
|
|
$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'";
|
|
// order by direction check
|
|
if (strtolower($order_by_direction) != 'desc')
|
|
{
|
|
$order_by_direction = 'asc';
|
|
}
|
|
// filter
|
|
if (is_array($filter_values))
|
|
{
|
|
foreach ($filter_values as $key => $value)
|
|
{
|
|
if ($key == 'name')
|
|
{
|
|
$where = " WHERE aa.name LIKE " . $this->db->escape("%$value%") . " COLLATE utf8_general_ci";
|
|
}
|
|
if ($key == 'datetime_from' && preg_match("/^\d{4}\-\d{2}\-\d{2}$/", $value))
|
|
{
|
|
$datetime_from_t1 = " AND t1.datetime >= '$value'";
|
|
$datetime_from_t2 = " AND t2.datetime >= '$value'";
|
|
}
|
|
if ($key == 'datetime_to' && preg_match("/^\d{4}\-\d{2}\-\d{2}$/", $value))
|
|
{
|
|
$datetime_to_t1 = " AND t1.datetime <= '$value'";
|
|
$datetime_to_t2 = " AND t2.datetime <= '$value'";
|
|
}
|
|
}
|
|
}
|
|
// query
|
|
return $this->db->query("
|
|
SELECT aa.id, aa.name, SUM(partial_balance) AS balance
|
|
FROM account_attributes aa
|
|
JOIN
|
|
(SELECT q2.account_attribute_id, (inbound - outbound) AS partial_balance FROM
|
|
(SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound FROM
|
|
(SELECT q0.*, IFNULL(SUM(amount), 0) AS outbound FROM
|
|
(SELECT a.id, a.account_attribute_id
|
|
FROM accounts a
|
|
) q0
|
|
LEFT JOIN transfers t1 ON q0.id = t1.origin_id $datetime_from_t1 $datetime_to_t1
|
|
GROUP BY q0.id
|
|
) q1
|
|
LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime_from_t2 $datetime_to_t2
|
|
GROUP BY q1.id
|
|
) q2
|
|
) q3 ON aa.id = q3.account_attribute_id
|
|
$where
|
|
GROUP BY q3.account_attribute_id
|
|
ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
|
|
LIMIT " . intval($limit_from) . ", " . intval($limit_results) . "
|
|
");
|
|
}
|
|
|
|
/**
|
|
* Gets accounting system count
|
|
*
|
|
* @param integer $filter_values
|
|
* @return integer
|
|
*/
|
|
public function get_accounting_system_count($filter_values = array())
|
|
{
|
|
$where = '';
|
|
// filter
|
|
if (is_array($filter_values) && array_key_exists('name', $filter_values))
|
|
{
|
|
$where = "AND aa.name LIKE " . $this->db->escape("%".$filter_values['name']."%")
|
|
. " COLLATE utf8_general_ci";
|
|
}
|
|
// query
|
|
return $this->db->query("
|
|
SELECT COUNT(*) AS total
|
|
FROM
|
|
(SELECT a.account_attribute_id
|
|
FROM account_attributes aa
|
|
JOIN accounts a ON aa.id = a.account_attribute_id
|
|
WHERE a.member_id = 1 $where
|
|
GROUP BY a.account_attribute_id
|
|
) q1
|
|
")->current()->total;
|
|
}
|
|
|
|
/**
|
|
* Gets account attributes. Returned are only that attributes which have at least one account.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return unknown_type
|
|
*/
|
|
public function get_account_attributes()
|
|
{
|
|
return $this->db->query("
|
|
SELECT aa.id, aa.name
|
|
FROM account_attributes aa
|
|
JOIN accounts a ON a.account_attribute_id = aa.id
|
|
GROUP BY aa.id
|
|
");
|
|
}
|
|
|
|
}
|