|
<?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/
|
|
*
|
|
*/
|
|
|
|
/**
|
|
* Account is own by each member.
|
|
*
|
|
* @package Model
|
|
*
|
|
* @property integer $id
|
|
* @property integer $member_id
|
|
* @property Member_Model $member
|
|
* @property string $name
|
|
* @property integer $account_attribute_id
|
|
* @property Account_attribute_Model $account_attribute
|
|
* @property double $balance
|
|
* @property string $comment
|
|
* @property integer $comments_thread_id
|
|
* @property Comments_thread_Model $comments_thread
|
|
* @property ORM_Iterator $transfers
|
|
* @property ORM_Iterator $bank_accounts
|
|
*/
|
|
class Account_Model extends ORM
|
|
{
|
|
// groups of double-entry accounts
|
|
|
|
/** Accounting system */
|
|
const ACCOUNTING_SYSTEM = 1;
|
|
/** Credit subaccounts grop */
|
|
const CREDIT = 2;
|
|
/** Project subaccounts group */
|
|
const PROJECT = 3;
|
|
/** Other accounts group */
|
|
const OTHER = 4;
|
|
|
|
protected $has_many = array('transfers','variable_symbols');
|
|
protected $belongs_to = array('member', 'account_attribute', 'comments_thread');
|
|
protected $has_and_belongs_to_many = array('bank_accounts');
|
|
|
|
/**
|
|
* Contruct of app, shutdown action logs by default
|
|
* @param type $id
|
|
*/
|
|
public function __construct($id = NULL)
|
|
{
|
|
parent::__construct($id);
|
|
// disable models
|
|
$this->set_logger(FALSE);
|
|
}
|
|
|
|
/**
|
|
* It gets double-entry accounts of given group.
|
|
* Groups are all credit, all project and other accounts of association.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_accounts(
|
|
$limit_from = 0, $limit_results = 20, $order_by = 'id',
|
|
$order_by_direction = 'asc', $filter_values = array(), $group = 4)
|
|
{
|
|
$where = 'WHERE ';
|
|
// order by direction check
|
|
if (strtolower($order_by_direction) != 'desc')
|
|
{
|
|
$order_by_direction = 'asc';
|
|
}
|
|
// 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;
|
|
}
|
|
// 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'";
|
|
// filter
|
|
if (is_array($filter_values))
|
|
{
|
|
foreach ($filter_values as $key => $value)
|
|
{
|
|
if ($key == 'name')
|
|
{
|
|
$where .= " AND a.name LIKE ".$this->db->escape("%$value%")." COLLATE utf8_general_ci";
|
|
}
|
|
else 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'";
|
|
}
|
|
else 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 q2.*, (inbound - outbound) AS balance
|
|
FROM
|
|
(
|
|
SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound
|
|
FROM
|
|
(
|
|
SELECT a.id, a.id AS aid, a.name, a.account_attribute_id,
|
|
m.name AS member_name, m.id AS member_id,
|
|
IFNULL(SUM(amount), 0) AS outbound,
|
|
a.comments_thread_id AS a_comments_thread_id
|
|
FROM accounts a
|
|
LEFT JOIN members m ON m.id = a.member_id
|
|
LEFT JOIN transfers t1 ON a.id = t1.origin_id $datetime_from_t1 $datetime_to_t1
|
|
$where
|
|
GROUP BY a.id
|
|
) q1
|
|
LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime_from_t2 $datetime_to_t2
|
|
GROUP BY q1.id
|
|
) q2
|
|
ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
|
|
LIMIT ".intval($limit_from).", ".intval($limit_results)."
|
|
");
|
|
}
|
|
|
|
/**
|
|
* It gets count of double-entry accounts of given group.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_accounts_count($filter_values = array(), $group = 4)
|
|
{
|
|
$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;
|
|
}
|
|
// filter
|
|
if (is_array($filter_values) && array_key_exists('name', $filter_values))
|
|
{
|
|
$where .= " AND a.name LIKE ".$this->db->escape("%" . $filter_values['name'] . "%")
|
|
. " COLLATE utf8_general_ci";
|
|
}
|
|
// get count
|
|
return $this->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.
|
|
* @param integer $origin
|
|
* @param integer $account_attribute_id
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_some_doubleentry_account_names($origin = null, $account_attribute_id = null)
|
|
{
|
|
// conditions
|
|
$cond_origin = "";
|
|
$where = "";
|
|
// make origin condition
|
|
if ($origin !== null)
|
|
{
|
|
$cond_origin = "AND a.id <> " . intval($origin) . "
|
|
AND (a.account_attribute_id = ".Account_attribute_Model::CREDIT."
|
|
OR a.account_attribute_id = ".Account_attribute_Model::OPERATING."
|
|
OR a.account_attribute_id = ".Account_attribute_Model::PROJECT.")";
|
|
}
|
|
// make account confition
|
|
if ($account_attribute_id)
|
|
{
|
|
$where = 'WHERE a.account_attribute_id=' . intval($account_attribute_id);
|
|
}
|
|
// query
|
|
return $this->db->query("
|
|
SELECT a.id, a.name, a.member_id, a.account_attribute_id,
|
|
IF(t.quarter IS NULL, concat(t.town, ' ', p.street_number),
|
|
concat(t.town, '-', t.quarter, ' ', p.street_number)) AS addr
|
|
FROM accounts a
|
|
JOIN members m ON
|
|
a.member_id=m.id
|
|
$cond_origin
|
|
JOIN address_points p ON m.address_point_id=p.id
|
|
JOIN towns t ON t.id=p.town_id $where
|
|
ORDER BY a.name
|
|
");
|
|
}
|
|
|
|
/**
|
|
* It gets balance of account.
|
|
* It subtracts all outbound transfers from all incoming transfers.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return float
|
|
*/
|
|
public function get_account_balance($account_id)
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT (IFNULL(SUM(amount), 0) - IFNULL(a.outbound, 0)) AS credit
|
|
FROM (
|
|
SELECT SUM(amount) AS outbound
|
|
FROM transfers
|
|
WHERE origin_id = ?
|
|
) a, transfers t
|
|
WHERE t.destination_id = ?
|
|
", array($account_id, $account_id));
|
|
|
|
return (float) ($result && $result->count()) ? $result->current()->credit : 0;
|
|
}
|
|
|
|
/**
|
|
* This function creates new record in the table "accounts".
|
|
*
|
|
* @author Tomas Dulik
|
|
* @param ineteger $attribute_id
|
|
* @param string $name
|
|
* @param integer $member_id
|
|
* @return Account_Model new object containing the new record model
|
|
*/
|
|
public static function create($attribute_id, $name, $member_id)
|
|
{
|
|
$account = new Account_Model();
|
|
$account->account_attribute_id = $attribute_id;
|
|
$account->name = $name;
|
|
$account->member_id = $member_id;
|
|
$account->save();
|
|
return $account;
|
|
}
|
|
|
|
/**
|
|
* Returns all accounts to deduct fee in date
|
|
*
|
|
* @author Michal Kliment
|
|
* @param string $date
|
|
* @return Mysql_Result object
|
|
*/
|
|
public function get_accounts_to_deduct($date)
|
|
{
|
|
// check
|
|
if (!preg_match("/^\d{4}\-\d{2}\-\d{2}$/", $date))
|
|
{
|
|
return FALSE;
|
|
}
|
|
// query
|
|
return $this->db->query("
|
|
SELECT a.id, a.balance, m.entrance_date, m.leaving_date,
|
|
IF(mf.fee IS NOT NULL, 1, 0) fee_is_set,
|
|
mf.fee,
|
|
mf.readonly AS fee_readonly,
|
|
mf.name AS fee_name,
|
|
IF(t.id IS NULL, 0, t.id) AS transfer_id
|
|
FROM accounts a
|
|
JOIN members m ON a.member_id = m.id
|
|
LEFT JOIN enum_types e ON e.id = m.type
|
|
LEFT JOIN
|
|
(
|
|
SELECT * FROM (SELECT f.fee, f.readonly, f.name, mf.member_id, priority FROM members_fees mf
|
|
LEFT JOIN fees f ON mf.fee_id = f.id
|
|
LEFT JOIN enum_types et ON f.type_id = et.id
|
|
WHERE et.value = 'Regular member fee'
|
|
AND mf.activation_date <= '$date'
|
|
AND mf.deactivation_date >= '$date'
|
|
ORDER BY member_id, priority) q
|
|
GROUP BY q.member_id
|
|
) mf ON m.id = mf.member_id
|
|
LEFT JOIN transfers t ON t.origin_id = a.id AND t.type = ? AND t.datetime = '$date'
|
|
WHERE m.id <> 1 AND m.entrance_date < '$date'
|
|
AND (m.leaving_date = '0000-00-00'
|
|
OR (m.leaving_date <> '0000-00-00' AND m.leaving_date > '$date'))
|
|
AND a.account_attribute_id = ?
|
|
", array(Transfer_Model::DEDUCT_MEMBER_FEE, Account_attribute_Model::CREDIT));
|
|
}
|
|
|
|
/**
|
|
* Function gets all members to deduct entrance fees.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_accounts_to_deduct_entrance_fees()
|
|
{
|
|
return $this->db->query("
|
|
SELECT a.id, m.entrance_fee, m.debt_payment_rate, m.entrance_date, t.id AS transfer_id, t.amount
|
|
FROM accounts a
|
|
JOIN members m ON a.member_id = m.id AND m.id <> 1
|
|
LEFT JOIN transfers t ON t.origin_id = a.id AND t.type = ?
|
|
WHERE a.account_attribute_id = ?
|
|
", array(Transfer_Model::DEDUCT_ENTRANCE_FEE, Account_attribute_Model::CREDIT));
|
|
}
|
|
|
|
|
|
/**
|
|
* Gets amount of deducted entrance fees of member's credit account.
|
|
* Used for deducting entrance fees with instalment.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return integer
|
|
*/
|
|
public function get_amount_of_entrance_fees($account_id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT IFNULL(SUM(t.amount), 0) AS amount
|
|
FROM transfers t
|
|
WHERE t.origin_id = ? AND t.type = ?
|
|
", array($account_id, Transfer_Model::DEDUCT_ENTRANCE_FEE))->current()->amount;
|
|
}
|
|
|
|
/**
|
|
* Gets information about one credit account, used for recounting
|
|
* entrance fee with or without instalment.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return Mysql_Result
|
|
*/
|
|
function get_account_to_recalculate_entrance_fees($account_id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT a.id, m.entrance_fee, m.debt_payment_rate, m.entrance_date
|
|
FROM accounts a
|
|
JOIN members m ON a.member_id = m.id AND m.id <> 1
|
|
WHERE a.account_attribute_id = ? AND a.id = ?
|
|
", array(Account_attribute_Model::CREDIT, $account_id));
|
|
}
|
|
|
|
/**
|
|
* Function deletes member fee deducting transfers of given account.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return integer Count of deleted transfers
|
|
* @throws Exception On error
|
|
*/
|
|
public function delete_deduct_transfers_of_account($account_id)
|
|
{
|
|
$transfers_count = 0;
|
|
|
|
// validates account_id
|
|
if (!$account_id || !is_numeric($account_id))
|
|
return $transfers_count;
|
|
|
|
$transfers = ORM::factory('transfer')->where(array
|
|
(
|
|
'type' => Transfer_Model::DEDUCT_MEMBER_FEE,
|
|
'origin_id' => $account_id
|
|
))->find_all();
|
|
|
|
foreach ($transfers as $transfer)
|
|
{
|
|
$transfers_count++;
|
|
$transfer->delete_throwable();
|
|
}
|
|
// recalculate balance of current credit account
|
|
if (!$this->recalculate_account_balance_of_account($account_id))
|
|
{
|
|
throw new Exception();
|
|
}
|
|
// 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 Exception();
|
|
}
|
|
|
|
return $transfers_count;
|
|
}
|
|
|
|
/**
|
|
* Function deletes entrance fee deducting transfers of given account.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return integer Count of deleted transfers
|
|
* @throws Exception On error
|
|
*/
|
|
public function delete_entrance_deduct_transfers_of_account($account_id)
|
|
{
|
|
$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++;
|
|
$transfer->delete_throwable();
|
|
}
|
|
// recalculate balance of current credit account
|
|
if (!$this->recalculate_account_balance_of_account($account_id))
|
|
{
|
|
throw new Exception();
|
|
}
|
|
// 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 Exception();
|
|
}
|
|
|
|
return $transfers_count;
|
|
}
|
|
|
|
/**
|
|
* Function deletes device fee deducting transfers of given account.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return integer Count of deleted transfers
|
|
* @throws Exception On error
|
|
*/
|
|
public function delete_device_deduct_transfers_of_account($account_id)
|
|
{
|
|
$transfers_count = 0;
|
|
|
|
// validates account_id
|
|
if (!$account_id || !is_numeric($account_id))
|
|
return $transfers_count;
|
|
|
|
$transfers = ORM::factory('transfer')->where(array
|
|
(
|
|
'type' => Transfer_Model::DEDUCT_DEVICE_FEE,
|
|
'origin_id' => $account_id
|
|
))->find_all();
|
|
|
|
foreach ($transfers as $transfer)
|
|
{
|
|
$transfers_count++;
|
|
$transfer->delete_throwable();
|
|
}
|
|
// recalculate balance of current credit account
|
|
if (!$this->recalculate_account_balance_of_account($account_id))
|
|
{
|
|
throw new Exception();
|
|
}
|
|
// 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 Exception();
|
|
}
|
|
|
|
return $transfers_count;
|
|
}
|
|
|
|
|
|
/**
|
|
* Finds accounts of members who have at least one device to repay.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_accounts_to_deduct_device_fees()
|
|
{
|
|
return $this->db->query("
|
|
SELECT a.id, SUM(d.price) AS total_price
|
|
FROM accounts a
|
|
JOIN members m ON a.member_id = m.id AND m.id <> 1
|
|
JOIN users u ON u.member_id = m.id
|
|
JOIN devices d ON d.user_id = u.id
|
|
WHERE a.account_attribute_id = ?
|
|
GROUP BY a.id
|
|
HAVING total_price > 0
|
|
", array(Account_attribute_Model::CREDIT));
|
|
}
|
|
|
|
/**
|
|
* Gets actually repaied amount of prices of all member's devices.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return integer
|
|
*/
|
|
public function get_amount_of_device_fees($account_id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT IFNULL(SUM(t.amount), 0) AS amount
|
|
FROM transfers t
|
|
WHERE t.origin_id = ? AND t.type = ?
|
|
", array($account_id, Transfer_Model::DEDUCT_DEVICE_FEE))->current()->amount;
|
|
}
|
|
|
|
/**
|
|
* Gets list of devices with repayments. Devices are ordered ascending by their buy date.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param unknown_type $account_id
|
|
* @return Mysql_Result
|
|
*/
|
|
public function get_devices_of_account($account_id)
|
|
{
|
|
return $this->db->query("
|
|
SELECT d.id, d.payment_rate, d.buy_date, d.price
|
|
FROM devices d
|
|
JOIN users u ON d.user_id = u.id
|
|
JOIN accounts a ON a.member_id = u.member_id AND u.member_id <> 1
|
|
WHERE a.id = ? AND d.price IS NOT NULL AND d.price > 0
|
|
ORDER BY d.buy_date ASC
|
|
", array($account_id));
|
|
}
|
|
|
|
|
|
/**
|
|
* Recalculates all account balances.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @return array[integer]
|
|
*/
|
|
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 ";
|
|
// array of ids to change
|
|
$ids = array();
|
|
foreach ($accounts as $account)
|
|
{
|
|
if ($account->balance != $account->calculated_balance)
|
|
{
|
|
$sql .= "WHEN $account->id THEN $account->calculated_balance ";
|
|
$ids[] = $account->id;
|
|
}
|
|
}
|
|
// are there some accounts with incorrect balances? save correct balances
|
|
if (count($ids) > 0)
|
|
{
|
|
$ids_with_commas = implode(',', $ids);
|
|
$sql .= "END WHERE id IN ($ids_with_commas)";
|
|
Database::instance()->query($sql);
|
|
}
|
|
// returns array of ids of corrected accounts
|
|
return $ids;
|
|
}
|
|
|
|
/**
|
|
* Recalculates account balance of single account.
|
|
*
|
|
* @author Jiri Svitak
|
|
* @param integer $account_id
|
|
* @return boolean
|
|
*/
|
|
public function recalculate_account_balance_of_account($account_id)
|
|
{
|
|
$balance = $this->get_account_balance($account_id);
|
|
|
|
return $this->db->query("
|
|
UPDATE accounts
|
|
SET balance = ?
|
|
WHERE id = ?
|
|
", array($balance, $account_id));
|
|
}
|
|
|
|
/**
|
|
* Get comment of this object
|
|
*
|
|
* @return string
|
|
*/
|
|
public function get_comments()
|
|
{
|
|
$result = $this->db->query("
|
|
SELECT GROUP_CONCAT(comment SEPARATOR ', \n\n') AS comment FROM
|
|
(
|
|
SELECT a.id, CONCAT(u.surname,' ',u.name,' (',SUBSTRING(c.datetime,1,10),'):\n',c.text) AS comment
|
|
FROM accounts a
|
|
JOIN comments c ON a.comments_thread_id = c.comments_thread_id
|
|
JOIN users u ON c.user_id = u.id
|
|
WHERE a.member_id = ? AND a.account_attribute_id = ?
|
|
ORDER BY c.datetime DESC
|
|
) AS q
|
|
GROUP BY q.id
|
|
", array($this->member_id, $this->account_attribute_id))->current();
|
|
|
|
return ($result) ? $result->comment: '';
|
|
}
|
|
|
|
}
|