Trac

source: trunk/inc/classes/BxDolAclQuery.php

Last change on this file was 16161, checked in by Anton Lesnikov, 2 years ago

Counters in Dropdown selectors

File size: 14.3 KB
Line 
1<?php
2/**
3 * @package     Dolphin Core
4 * @copyright   Copyright (c) BoonEx Pty Limited - http://www.boonex.com/
5 * @license     CC-BY - http://creativecommons.org/licenses/by/3.0/
6 */
7defined('BX_DOL') or die('hack attempt');
8
9bx_import('BxDolDb');
10
11class BxDolAclQuery extends BxDolDb {
12    function __construct() {
13        if (isset($GLOBALS['bxDolClasses'][get_class($this)]))
14            trigger_error ('Multiple instances are not allowed for the class: ' . get_class($this), E_USER_ERROR);
15
16        parent::BxDolDb();
17    }
18
19    /**
20     * Prevent cloning the instance
21     */
22    public function __clone() {
23        if (isset($GLOBALS['bxDolClasses'][get_class($this)]))
24            trigger_error('Clone is not allowed for the class: ' . get_class($this), E_USER_ERROR);
25    }
26
27    /**
28     * Get singleton instance of the class
29     */
30    static function getInstance() {
31        if(!isset($GLOBALS['bxDolClasses'][__CLASS__]))
32            $GLOBALS['bxDolClasses'][__CLASS__] = new BxDolAclQuery();
33
34        return $GLOBALS['bxDolClasses'][__CLASS__];
35    }
36
37    function getLevels($aParams, &$aItems, $bReturnCount = true) {
38        $aMethod = array('name' => 'getAll', 'params' => array(0 => 'query'));
39        $sSelectClause = $sJoinClause = $sWhereClause = $sOrderClause = $sLimitClause = "";
40
41        if(!isset($aParams['order']) || empty($aParams['order']))
42           $sOrderClause = "ORDER BY `tal`.`Order` ASC";
43
44        switch($aParams['type']) {
45            case 'by_id':
46                $aMethod['name'] = 'getRow';
47                $sWhereClause .= $this->prepare("AND `tal`.`ID`=?", $aParams['value']);
48                $sLimitClause .= "LIMIT 1";
49                break;
50            case 'all_active':
51                $sWhereClause .= "AND `tal`.`Active`='yes'";
52                break;
53            case 'all_active_purchasble_pair':
54                $aMethod['name'] = "getPairs";
55                $aMethod['params'][1] = 'id';
56                $aMethod['params'][2] = 'name';
57                $sWhereClause .= "AND `tal`.`Active`='yes' AND `tal`.`Purchasable`='yes'";
58                break;
59            case 'all_active_pair':
60                $aMethod['name'] = "getPairs";
61                $aMethod['params'][1] = 'id';
62                $aMethod['params'][2] = 'name';
63                $sWhereClause .= "AND `tal`.`Active`='yes'";
64                break;   
65            case 'all_pair':
66                $aMethod['name'] = "getPairs";
67                $aMethod['params'][1] = 'id';
68                $aMethod['params'][2] = 'name';
69                break;
70            case 'all_order_id':
71                $sOrderClause = "ORDER BY `tal`.`ID` ASC";
72                break;
73            case 'all':
74                break;
75        }
76
77        $aMethod['params'][0] = "SELECT " . ($bReturnCount ? "SQL_CALC_FOUND_ROWS" : "") . "
78                `tal`.`ID` AS `id`,
79                `tal`.`Name` AS `name`,
80                `tal`.`Icon` AS `icon`,
81                `tal`.`Description` AS `description`,
82                `tal`.`Active` AS `active`,
83                `tal`.`Purchasable` AS `purchasable`,
84                `tal`.`Removable` AS `removable`,
85                `tal`.`QuotaSize` AS `quota_size`,
86                `tal`.`QuotaNumber` AS `quota_number`,
87                `tal`.`QuotaMaxFileSize` AS `quota_max_file_size`,
88                `tal`.`Order` AS `order`" . $sSelectClause . "
89            FROM `sys_acl_levels` AS `tal` " . $sJoinClause . "
90            WHERE 1 " . $sWhereClause . " " . $sOrderClause . " " . $sLimitClause;
91        $aItems = call_user_func_array(array($this, $aMethod['name']), $aMethod['params']);
92
93        if(!$bReturnCount)
94            return !empty($aItems);
95
96        return (int)$this->getOne("SELECT FOUND_ROWS()");
97    }
98
99    function getActions($aParams, &$aItems, $bReturnCount = true) {
100        $aMethod = array('name' => 'getAll', 'params' => array(0 => 'query'));
101        $sSelectClause = $sJoinClause = $sWhereClause = $sGroupClause = $sOrderClause = $sLimitClause = "";
102
103        if(!isset($aParams['order']) || empty($aParams['order']))
104           $sOrderClause = "ORDER BY `taa`.`Title` ASC";
105
106        switch($aParams['type']) {
107            case 'by_names_and_module':
108                $sWhereClause .= " AND `taa`.`Name` IN(" . $this->implode_escape($aParams['value']) . ") " . $this->prepare(" AND `taa`.`Module` = ? ", $aParams['module']);
109                break;
110            case 'by_names':
111                $sWhereClause .= " AND `taa`.`Name` IN(" . $this->implode_escape($aParams['value']) . ")";
112                break;
113            case 'by_level_id':
114                $sSelectClause .= ", `tam`.`AllowedCount` AS `allowed_count`, `tam`.`AllowedPeriodLen` AS `allowed_period_len` ";
115                $sJoinClause .= "LEFT JOIN `sys_acl_matrix` AS `tam` ON `taa`.`ID`=`tam`.`IDAction` ";
116                $sWhereClause .= $this->prepare("AND `tam`.`IDLevel`=? AND (`taa`.`DisabledForLevels`='0' OR `taa`.`DisabledForLevels`&?=0)", $aParams['value'], pow(2, ($aParams['value'] - 1)));
117                break;
118            case 'by_level_id_key_id':
119                $aMethod['name'] = 'getAllWithKey';
120                $aMethod['params'][1] = 'id';
121                $sSelectClause .= ", `tam`.`AllowedCount` AS `allowed_count`, `tam`.`AllowedPeriodLen` AS `allowed_period_len` ";
122                $sJoinClause .= "LEFT JOIN `sys_acl_matrix` AS `tam` ON `taa`.`ID`=`tam`.`IDAction` ";
123                $sWhereClause .= $this->prepare("AND `tam`.`IDLevel`=?", $aParams['value']);
124                break;
125            case 'counter_by_modules':
126                $aMethod['name'] = 'getPairs';
127                $aMethod['params'][1] = 'module';
128                $aMethod['params'][2] = 'counter';
129                $sSelectClause = ", COUNT(*) AS `counter`";
130                $sGroupClause = "GROUP BY `taa`.`Module`";
131                break;
132            case 'counter_by_levels':
133                $aMethod['name'] = 'getPairs';
134                $aMethod['params'][1] = 'level_id';
135                $aMethod['params'][2] = 'counter';
136                $sSelectClause = ", `tam`.`IDLevel` AS `level_id`, COUNT(`tam`.`IDAction`) AS `counter`";
137                $sJoinClause = "LEFT JOIN `sys_acl_matrix` AS `tam` ON `taa`.`ID`=`tam`.`IDAction` AND (`taa`.`DisabledForLevels`='0' OR `taa`.`DisabledForLevels`&POW(2, `tam`.`IDLevel`-1)=0) ";
138                $sGroupClause = "GROUP BY `tam`.`IDLevel`";
139                break;
140        }
141
142        $aMethod['params'][0] = "SELECT " . ($bReturnCount ? "SQL_CALC_FOUND_ROWS" : "") . "
143                `taa`.`ID` AS `id`,
144                `taa`.`Module` AS `module`,
145                `taa`.`Name` AS `name`,
146                `taa`.`Title` AS `title`,
147                `taa`.`Countable` AS `countable`,
148                `taa`.`DisabledForLevels` AS `disabled_for_levels`" . $sSelectClause . "
149            FROM `sys_acl_actions` AS `taa` " . $sJoinClause . "
150            WHERE 1 " . $sWhereClause . " " . $sGroupClause . " " . $sOrderClause . " " . $sLimitClause;
151        $aItems = call_user_func_array(array($this, $aMethod['name']), $aMethod['params']);
152
153        if(!$bReturnCount)
154            return !empty($aItems);
155
156        return (int)$this->getOne("SELECT FOUND_ROWS()");
157    }
158
159    function getPrices($aParams, &$aItems, $bReturnCount = true) {
160        $aMethod = array('name' => 'getAll', 'params' => array(0 => 'query'));
161        $sSelectClause = $sJoinClause = $sWhereClause = $sOrderClause = $sLimitClause = "";
162
163        if(!isset($aParams['order']) || empty($aParams['order']))
164           $sOrderClause = "ORDER BY `tap`.`Order` ASC";
165
166        switch($aParams['type']) {
167            case 'by_id':
168                $aMethod['name'] = 'getRow';
169                $sWhereClause .= $this->prepare("AND `tap`.`id`=?", $aParams['value']);
170                break;
171            case 'by_level_id':
172                $sWhereClause .= $this->prepare("AND `tap`.`IDLevel`=?", $aParams['value']);
173                break;
174            case 'by_level_id_pair':
175                $aMethod['name'] = "getPairs";
176                $aMethod['params'][1] = 'days';
177                $aMethod['params'][2] = 'price';
178                $sWhereClause .= $this->prepare("AND `tap`.`IDLevel`=?", $aParams['value']);
179                break;
180            case 'by_level_id_duration':
181                $aMethod['name'] = 'getRow';
182                $sWhereClause .= $this->prepare("AND `tap`.`IDLevel`=? AND `tap`.`Days`=?", $aParams['level_id'], $aParams['days']);
183                break;
184            case 'counter_by_levels':
185                $aMethod['name'] = 'getPairs';
186                $aMethod['params'][1] = 'level_id';
187                $aMethod['params'][2] = 'counter';
188                $sSelectClause = ", COUNT(*) AS `counter`";
189                $sGroupClause = "GROUP BY `tap`.`IDLevel`";
190                break;
191        }
192
193        $aMethod['params'][0] = "SELECT " . ($bReturnCount ? "SQL_CALC_FOUND_ROWS" : "") . "
194                `tap`.`id` AS `id`,
195                `tap`.`IDLevel` AS `level_id`,
196                `tap`.`Days` AS `days`,
197                `tap`.`Price` AS `price`,
198                `tap`.`Order` AS `order`" . $sSelectClause . "
199            FROM `sys_acl_level_prices` AS `tap` " . $sJoinClause . "
200            WHERE 1 " . $sWhereClause . " " . $sOrderClause . " " . $sLimitClause;
201        $aItems = call_user_func_array(array($this, $aMethod['name']), $aMethod['params']);
202
203        if(!$bReturnCount)
204            return !empty($aItems);
205
206        return (int)$this->getOne("SELECT FOUND_ROWS()");
207    }
208
209    /**
210     * Fetch the last purchased/assigned membership that is still active for the given profile.
211     *
212     * NOTE. Don't use cache here, because it's causing an error, if a number of memberrship levels are purchased at the same time.
213     *
214     * fromMemory returns the same DateExpires because buyMembership function is called in cycle in the same session.
215     */
216    function getLevelCurrent($iProfileId, $iTime = 0) {
217        $iTime = $iTime == 0 ? time() : (int)$iTime;
218
219        $sSql = $this->prepare("
220            SELECT  `sys_acl_levels_members`.`IDLevel` as `id`,
221                    `sys_acl_levels`.`Name` AS `name`,
222                    `sys_acl_levels`.`QuotaSize` AS `quota_size`,
223                    `sys_acl_levels`.`QuotaNumber` AS `quota_number`,
224                    `sys_acl_levels`.`QuotaMaxFileSize` AS `quota_max_file_size`,
225                    UNIX_TIMESTAMP(`sys_acl_levels_members`.`DateStarts`) as `date_starts`,
226                    UNIX_TIMESTAMP(`sys_acl_levels_members`.`DateExpires`) as `date_expires`,
227                    `sys_acl_levels_members`.`TransactionID` AS `transaction_id`,
228                    `sys_profiles`.`status`
229            FROM `sys_acl_levels_members`
230            RIGHT JOIN `sys_profiles` ON `sys_acl_levels_members`.IDMember = `sys_profiles`.`id`
231                AND (`sys_acl_levels_members`.DateStarts IS NULL OR `sys_acl_levels_members`.DateStarts <= FROM_UNIXTIME(?))
232                AND (`sys_acl_levels_members`.DateExpires IS NULL OR `sys_acl_levels_members`.DateExpires > FROM_UNIXTIME(?))
233            LEFT JOIN `sys_acl_levels` ON `sys_acl_levels_members`.IDLevel = `sys_acl_levels`.ID
234            WHERE `sys_profiles`.`id` = ?
235            ORDER BY `sys_acl_levels_members`.DateStarts DESC
236            LIMIT 1", $iTime, $iTime, $iProfileId);
237
238        return $this->getRow($sSql);
239    }
240
241    function getLevelByIdCached($iLevel) {
242        $sQuery = $this->prepare("SELECT
243                `tal`.`ID` AS `id`,
244                `tal`.`Name` AS `name`,
245                `tal`.`QuotaSize` AS `quota_size`,
246                `tal`.`QuotaNumber` AS `quota_number`,
247                `tal`.`QuotaMaxFileSize` AS `quota_max_file_size`
248            FROM `sys_acl_levels` AS `tal`
249            WHERE `tal`.`ID`=?
250            LIMIT 1", $iLevel);
251        return $this->fromCache('sys_acl_levels' . $iLevel, 'getRow', $sQuery);
252    }
253
254    function getAction($iMembershipId, $iActionId) {
255        $sQuery = $this->prepare("SELECT
256                `tam`.`IDAction` AS `id`,
257                `taa`.`Name` AS `name`, 
258                `taa`.`Title` AS `title`,
259                `tam`.`AllowedCount` AS `allowed_count`,
260                `tam`.`AllowedPeriodLen` AS `allowed_period_len`,
261                UNIX_TIMESTAMP(`tam`.`AllowedPeriodStart`) as `allowed_period_start`,
262                UNIX_TIMESTAMP(`tam`.`AllowedPeriodEnd`) as `allowed_period_end`,
263                `tam`.`AdditionalParamValue` AS `additional_param_value`
264            FROM `sys_acl_actions` AS `taa`
265            LEFT JOIN `sys_acl_matrix` AS `tam` ON `tam`.`IDAction` = `taa`.`ID` AND `tam`.`IDLevel` = ?
266            WHERE `taa`.`ID` = ?", $iMembershipId, $iActionId);
267        return $this->getRow($sQuery);
268    }
269
270    function getActionTrack($iActionId, $iProfileId) {
271        $sQuery = $this->prepare("SELECT
272                `taat`.`ActionsLeft` AS `actions_left`,
273                UNIX_TIMESTAMP(`taat`.`ValidSince`) as `valid_since`
274            FROM `sys_acl_actions_track` AS `taat`
275            WHERE `taat`.`IDAction`=? AND `taat`.`IDMember`=?", $iActionId, $iProfileId);
276        return $this->getRow($sQuery);
277    }
278
279    function insertActionTarck($iActionId, $iProfileId, $iActionsLeft, $iValidSince) {
280        $sQuery = $this->prepare("INSERT INTO `sys_acl_actions_track`(`IDAction`, `IDMember`, `ActionsLeft`, `ValidSince`) VALUES (?, ?, ?, FROM_UNIXTIME(?))", $iActionId, $iProfileId, $iActionsLeft, $iValidSince);
281        return (int)$this->query($sQuery) > 0;
282    }
283
284    function updateActionTrack($iActionId, $iProfileId, $iActionsLeft, $iValidSince = 0) {
285        $sUpdateAddon = "";
286        if($iValidSince != 0)
287            $sUpdateAddon = $this->prepare(", ValidSince=FROM_UNIXTIME(?)", $iValidSince);
288
289        $sQuery = $this->prepare("UPDATE `sys_acl_actions_track` SET `ActionsLeft`=?" . $sUpdateAddon . " WHERE `IDAction`=? AND `IDMember`=?", $iActionsLeft, $iActionId, $iProfileId);
290        return (int)$this->query($sQuery) > 0;
291    }
292
293    function insertLevelByProfileId($iProfileId, $iMembershipId, $iDateStarts, $iDateExpires, $sTransactionId) {
294        $sQuery = $this->prepare("INSERT `sys_acl_levels_members` (`IDMember`, `IDLevel`, `DateStarts`, `DateExpires`, `TransactionID`) VALUES (?, ?, FROM_UNIXTIME(?), FROM_UNIXTIME(?), '?')", $iProfileId, $iMembershipId, $iDateStarts, $iDateExpires, $sTransactionId);
295        return (int)$this->query($sQuery) > 0;
296    }
297
298    function deleteLevelByProfileId($iProfileId, $bAll = false) {
299        $sQuery = $this->prepare("DELETE FROM `sys_acl_levels_members` WHERE `IDMember`=?" . ($bAll ? " AND (`DateExpires` IS NULL OR `DateExpires`>NOW())" : ""), $iProfileId);
300        return (int)$this->query($sQuery) > 0;
301    }
302}
303/** @} */
Note: See TracBrowser for help on using the repository browser.