Projekt

Obecné

Profil

« Předchozí | Další » 

Revize c1bdc1c4

Přidáno uživatelem Michal Kliment před více než 9 roky(ů)

Release 1.1.0

Zobrazit rozdíly:

application/models/account.php
*/
public function get_accounts(
$limit_from = 0, $limit_results = 20, $order_by = 'id',
$order_by_direction = 'asc', $filter_values = array(), $group = 4)
$order_by_direction = 'asc', $filter_sql = '', $date_sql = '', $group = 4)
{
$where = 'WHERE ';
$datetime = '';
// order by direction check
if (strtolower($order_by_direction) != 'desc')
{
......
$datetime_from_t2 = " AND t2.datetime >= '0000-00-00'";
$datetime_to_t2 = " AND t2.datetime <= '9999-12-31'";
// filter
if (is_array($filter_values))
if (!empty($filter_sql))
{
foreach ($filter_values as $key => $value)
if (empty($where))
{
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'";
}
$where = "WHERE $filter_sql";
}
else
{
$where .= " AND $filter_sql";
}
}
if (!empty($date_sql))
{
$datetime = " AND $date_sql";
}
// query
return $this->db->query("
SELECT q2.*, (inbound - outbound) AS balance
......
SELECT q1.*, IFNULL(SUM(amount), 0) AS inbound
FROM
(
SELECT a.id, a.id AS aid, a.name, a.account_attribute_id,
SELECT aa.id, aa.id AS aid, aa.name, aa.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
aa.comments_thread_id AS a_comments_thread_id
FROM accounts aa
LEFT JOIN members m ON m.id = aa.member_id
LEFT JOIN transfers t1 ON aa.id = t1.origin_id $datetime
$where
GROUP BY a.id
GROUP BY aa.id
) q1
LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime_from_t2 $datetime_to_t2
LEFT JOIN transfers t2 ON q1.id = t2.destination_id $datetime
GROUP BY q1.id
) q2
ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
......
* @author Jiri Svitak
* @return Mysql_Result
*/
public function get_accounts_count($filter_values = array(), $group = 4)
public function get_accounts_count($filter_sql = '', $group = 4)
{
$where = 'WHERE ';
// group - project, credit and other accounts
......
$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))
if(!empty($filter_sql))
{
$where .= " AND a.name LIKE ".$this->db->escape("%" . $filter_values['name'] . "%")
. " COLLATE utf8_general_ci";
$where .= " AND $filter_sql";
}
// get count
return $this->db->query("
SELECT COUNT(*) AS total
FROM accounts a
FROM accounts aa
$where
")->current()->total;
}
......
// 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
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
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'
......
* @author Jiri Svitak
* @return Mysql_Result
*/
public function get_accounts_to_deduct_entrance_fees()
public function get_accounts_to_deduct_entrance_fees($deduct_date)
{
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));
SELECT a.id AS id,
IF (debt > debt_payment_rate, debt_payment_rate, debt) AS amount
FROM
(
SELECT a.id,
IFNULL(m.entrance_fee, 0) - IFNULL(SUM(t.amount),0) AS debt,
debt_payment_rate
FROM accounts a
JOIN members m ON a.member_id = m.id
LEFT JOIN transfers t ON t.origin_id = a.id AND t.type = ?
WHERE a.account_attribute_id = ?
AND m.entrance_fee > 0 AND m.type <> ?
AND m.entrance_date < ? AND
(
m.type <> ? OR m.leaving_date > ?
) AND a.id NOT IN
(
SELECT t.origin_id
FROM transfers t
WHERE type = ? AND datetime = ?
)
GROUP BY a.id
) a
WHERE a.debt > 0
", array
(
Transfer_Model::DEDUCT_ENTRANCE_FEE,
Account_attribute_Model::CREDIT,
Member_Model::TYPE_APPLICANT,
$deduct_date,
Member_Model::TYPE_FORMER,
$deduct_date,
Transfer_Model::DEDUCT_ENTRANCE_FEE,
$deduct_date
));
}
......
* @author Jiri Svitak
* @return Mysql_Result
*/
public function get_accounts_to_deduct_device_fees()
public function get_accounts_to_deduct_device_fees($deduct_date)
{
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));
SELECT a.id, IF (debt > payment_rate, payment_rate, debt) AS amount
FROM
(
SELECT a.id, IFNULL(d.price, 0) - IFNULL(SUM(t.amount),0) AS debt,
payment_rate
FROM accounts a
JOIN members m ON a.member_id = m.id
JOIN users u ON u.member_id = m.id
JOIN devices d ON d.user_id = u.id AND d.price IS NOT NULL AND d.price > 0
LEFT JOIN transfers t ON t.origin_id = a.id AND t.type = ?
WHERE a.id NOT IN
(
SELECT t.origin_id
FROM transfers t
WHERE type = ? AND datetime = ?
)
GROUP BY a.id
) a
WHERE a.debt > 0
", array
(
Transfer_Model::DEDUCT_DEVICE_FEE,
Transfer_Model::DEDUCT_DEVICE_FEE,
$deduct_date
));
}
/**
......
return ($result) ? $result->comment: '';
}
/**
* Returns account by given attribute and member
*
* @author Michal Kliment
* @param integer $account_attribute_id
* @param integer $member_id
* @return Account_Model
*/
public function get_account_by_account_attribute_and_member ($account_attribute_id, $member_id)
{
return $this
->where('account_attribute_id', $account_attribute_id)
->where('member_id', $member_id)
->find();
}
/**
* Gets account by account attribute ID.
* Creates new instance.
*
* @param integer $account_attribute_id
* @return Account_Model
*/
public function get_account_by_attribute($account_attribute_id)
{
return ORM::factory('account')
->where('account_attribute_id', $account_attribute_id)
->find();
}
}

Také k dispozici: Unified diff