Cheetah
ChWsbSubscriptionQuery.php
Go to the documentation of this file.
1 <?php
2 
8 ch_import('ChWsbDb');
9 ch_import('ChWsbEmailTemplates');
10 
12 {
13  var $_sPrefix;
15 
20  {
21  parent::__construct();
22 
23  $this->_oSubscription = &$oSubscription;
24  $this->_sPrefix = 'sys_sbs_';
25  }
26  function isSubscribed($aParams)
27  {
28  $iType = CH_WSB_SBS_TYPE_MEMBER;
29  if(!isset($aParams['user_id']) || (int)$aParams['user_id'] == 0) {
30  $aParams['user_id'] = 0;
31  if(!empty($aParams['name']) && !empty($aParams['email']))
32  $aParams['user_id'] = (int)(int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `name`='" . $aParams['name'] . "' AND `email`='" . $aParams['email'] . "' LIMIT 1");
33 
34  $iType = CH_WSB_SBS_TYPE_VISITOR;
35  }
36 
37  $sSql = "SELECT
38  `tse`.`id` AS `id`
39  FROM `" . $this->_sPrefix . "entries` AS `tse`
40  LEFT JOIN `" . $this->_sPrefix . "types` AS `tst` ON `tse`.`subscription_id`=`tst`.`id`
41  WHERE
42  `tst`.`unit`='" . $aParams['unit'] . "' AND
43  " . (!empty($aParams['action']) ? "`tst`.`action`='" . $aParams['action'] . "' AND " : "") . "
44  `tse`.`subscriber_id`='" . $aParams['user_id'] . "' AND
45  `tse`.`subscriber_type`='" . $iType . "'" .
46  ((int)$aParams['object_id'] != 0 ? " AND `tse`.`object_id`='" . $aParams['object_id'] . "'" : "");
47  return !empty($aParams['user_id']) && (int)$this->getOne($sSql) > 0;
48  }
49  function getSubscription($sUnit, $sAction)
50  {
51  $sSql = "SELECT
52  `id` AS `id`,
53  `unit` AS `unit`,
54  `action` AS `action`,
55  `template` AS `template`,
56  `params` AS `params`
57  FROM `" . $this->_sPrefix . "types`
58  WHERE `unit`= ? AND `action`= ?
59  LIMIT 1";
60  return $this->getRow($sSql, [$sUnit, $sAction]);
61  }
62  function getSubscriptions($sUnit, $sAction = '')
63  {
64  $sSql = "SELECT
65  `id` AS `id`,
66  `unit` AS `unit`,
67  `action` AS `action`,
68  `template` AS `template`,
69  `params` AS `params`
70  FROM `" . $this->_sPrefix . "types`
71  WHERE `unit`= ? " . (!empty($sAction) ? " AND `action`='" . $sAction . "'" : "");
72  return $this->getAll($sSql, [$sUnit]);
73  }
74  function getSubscriptionsByUser($iUserId)
75  {
76  $sSql = "SELECT
77  `tt`.`id` AS `id`,
78  `tt`.`unit` AS `unit`,
79  `tt`.`action` AS `action`,
80  `tt`.`params` AS `params`,
81  (SELECT
82  GROUP_CONCAT(`ste`.`id` ORDER BY `ste`.`id`)
83  FROM `" . $this->_sPrefix . "entries` AS `ste`
84  LEFT JOIN `" . $this->_sPrefix . "types` AS `stt` ON `ste`.`subscription_id`=`stt`.`id`
85  WHERE `stt`.`unit`=`tt`.`unit` AND `ste`.`object_id`=`te`.`object_id`
86  GROUP BY `stt`.`unit`, `ste`.`object_id`) AS `entry_id`,
87  `te`.`object_id` AS `object_id`
88  FROM `" . $this->_sPrefix . "entries` AS `te`
89  LEFT JOIN `" . $this->_sPrefix . "types` AS `tt` ON `te`.`subscription_id`=`tt`.`id`
90  WHERE `tt`.`action`='' AND `te`.`subscriber_id`= ? AND `te`.`subscriber_type`='" . CH_WSB_SBS_TYPE_MEMBER . "'
91  ORDER BY `tt`.`unit`, `te`.`object_id`";
92  return $this->getAll($sSql, [$iUserId]);
93  }
94  function addSubscription($aParams)
95  {
96  switch($aParams['type']) {
98  $sUserName = process_db_input($aParams['user_name'], CH_TAGS_STRIP);
99  $sUserEmail = process_db_input($aParams['user_email'], CH_TAGS_STRIP);
100  if(empty($sUserName) || empty($sUserEmail) || !(bool)preg_match('/^([a-z0-9\+\_\-\.]+)@([a-z0-9\+\_\-\.]+)$/i', $sUserEmail))
101  return array('code' => 4, 'message' => _t('_sys_txt_sbs_empty_name_email'));
102 
103  $iUserId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `email`='" . $sUserEmail . "' LIMIT 1");
104  if($iUserId != 0)
105  break;
106 
107  $mixedResult = $this->query("INSERT INTO `" . $this->_sPrefix . "users`(`name`, `email`, `date`) VALUES('" . $sUserName . "', '" . $sUserEmail . "', UNIX_TIMESTAMP())");
108  if($mixedResult === false)
109  return array('code' => 1, 'message' => _t('_sys_txt_sbs_cannot_save_visitor'));
110 
111  $iUserId = (int)$this->lastId();
112  break;
114  $aProfileInfo = getProfileInfo((int)$aParams['user_id']);
115 
116  $iUserId = $aProfileInfo['ID'];
117  $sUserName = getNickName($aProfileInfo['ID']);
118  $sUserEmail = $aProfileInfo['Email'];
119  break;
120  }
121 
122  $aSubscriptions = $this->getSubscriptions($aParams['unit'], $aParams['action']);
123  if(!is_array($aSubscriptions) || empty($aSubscriptions))
124  return array('code' => 2, 'message' => _t('_sys_txt_sbs_cannot_find_subscription'));
125 
126  $aTemplateParams = array();
127  $aResults = array();
128  foreach($aSubscriptions as $aSubscription) {
129  if($aSubscription['action'] == $aParams['action'] && !empty($aSubscription['params'])) {
130  $oFunction = function($arg1, $arg2, $arg3) use ($aSubscription) {
131  return eval($aSubscription['params']);
132  };
133 
134  $aUnitParams = $oFunction($aParams['unit'], $aParams['action'], $aParams['object_id']);
135  }
136 
137  /*
138  if(empty($aSubscription['action']))
139  continue;
140  */
141 
142  $iEntryId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $aParams['type'] . "' AND `subscription_id`='" . $aSubscription['id'] . "' AND `object_id`='" . (int)$aParams['object_id'] . "' LIMIT 1");
143  if(!empty($iEntryId))
144  return array('code' => 3, 'message' => _t('_sys_txt_sbs_already_subscribed'));
145 
146  $iResult = (int)$this->query("INSERT INTO `" . $this->_sPrefix . "entries`(`subscriber_id`, `subscriber_type`, `subscription_id`, `object_id`) VALUES('" . $iUserId . "', '" . $aParams['type'] . "', '" . $aSubscription['id'] . "', '" . (int)$aParams['object_id'] . "')");
147  if($iResult > 0)
148  $aResults[] = $this->lastId();
149  }
150 
151  if(count($aResults) > 0) {
152  $oEmailTemplate = new ChWsbEmailTemplates();
153  $aTemplateParams = array (
154  'RealName' => $sUserName,
155  'SysUnsubscribeLink' => $this->_oSubscription->_getUnsubscribeLink($aResults)
156  );
157  if(isset($aUnitParams['template']))
158  $aTemplateParams = array_merge($aTemplateParams, $aUnitParams['template']);
159 
160  $aMail = $oEmailTemplate->parseTemplate('t_Subscription', $aTemplateParams);
161  sendMail($sUserEmail, $aMail['subject'], $aMail['body']);
162 
163  $aResult = array('code' => 0, 'message' => _t('_sys_txt_sbs_success_subscribe'));
164  } else
165  $aResult = array('code' => 5, 'message' => _t('_sys_txt_sbs_error_occured'));
166 
167  return $aResult;
168  }
169  function deleteSubscription($aParams)
170  {
171  switch($aParams['type']) {
173  if(isset($aParams['user_id']))
174  $iUserId = (int)$aParams['user_id'];
175  else if(isset($aParams['user_name']) && isset($aParams['user_email']))
176  $iUserId = (int)$this->getOne("SELECT `id` FROM `" . $this->_sPrefix . "users` WHERE `name`='" . process_db_input($aParams['user_name'], CH_TAGS_STRIP) . "' AND `email`='" . process_db_input($aParams['user_email'], CH_TAGS_STRIP) . "' LIMIT 1");
177 
178  $iUserType = CH_WSB_SBS_TYPE_VISITOR;
179  break;
181  $iUserId = (int)$aParams['user_id'];
182  $iUserType = CH_WSB_SBS_TYPE_MEMBER;
183  break;
184  }
185 
186  $iResult = 0;
187  //--- Unsubscribe when the button is clicked ---//
188  if(isset($aParams['unit']) && isset($aParams['action'])) {
189  $aSubscriptions = $this->getSubscriptions($aParams['unit'], $aParams['action']);
190  if(!is_array($aSubscriptions) || empty($aSubscriptions))
191  return array('code' => 2, 'message' => _t('_sys_txt_sbs_cannot_find_subscription'));
192 
193  foreach($aSubscriptions as $aSubscription)
194  $iResult += (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $iUserType . "' AND `subscription_id`='" . $aSubscription['id'] . "'" . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . (int)$aParams['object_id'] . "'" : ""));
195  }
196  //--- Unsubscribe when the object is deleted ---//
197  else if(isset($aParams['unit']) && isset($aParams['object_id'])) {
198  $aSubscriptions = $this->getSubscriptions($aParams['unit']);
199  if(is_array($aSubscriptions) && !empty($aSubscriptions)) {
200  foreach($aSubscriptions as $aSubscription)
201  $aIds[] = $aSubscription['id'];
202 
203  $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscription_id` IN ('" . implode("','", $aIds) . "') AND `object_id`='" . (int)$aParams['object_id'] . "'");
204  } else
205  $iResult = 0;
206  }
207  //--- Unsubscribe when the link with SID is clicked ---//
208  else if(isset($aParams['sid'])) {
209  $aIds = explode(",", base64_decode(urldecode($aParams['sid'])));
210  if(is_array($aIds) && !empty($aIds)) {
211  foreach ($aIds as $k => $v)
212  $aIds[$k] = (int)$v;
213 
214  list($iUserId, $iUserType) = $this->getRow("SELECT `subscriber_id`, `subscriber_type` FROM `" . $this->_sPrefix . "entries` WHERE `id`= ? LIMIT 1", [$aIds[0]], PDO::FETCH_NUM);
215 
216  $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `id` IN ('" . implode("','", $aIds) . "')");
217  }
218  }
219  //--- Unsubscribe the user from all subscriptions ---//
220  else
221  $iResult = (int)$this->query("DELETE FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . $iUserType . "'");
222 
223  if($iUserType == CH_WSB_SBS_TYPE_VISITOR || (isset($aParams['unit']) && isset($aParams['object_id']))) {
224  $iSbsEntries = (int)$this->getOne("SELECT COUNT(`id`) FROM `" . $this->_sPrefix . "entries` WHERE `subscriber_id`='" . $iUserId . "' AND `subscriber_type`='" . CH_WSB_SBS_TYPE_VISITOR . "' LIMIT 1");
225  if($iSbsEntries == 0)
226  $this->query("DELETE FROM `" . $this->_sPrefix . "users` WHERE `id`='" . $iUserId . "' LIMIT 1");
227  }
228 
229  return $iResult > 0 ? array('code' => 0, 'message' => _t('_sys_txt_sbs_success_unsubscribe')) : array('code' => 4, 'message' => _t('_sys_txt_sbs_already_unsubscribed'));
230  }
231  function sendDelivery($aParams)
232  {
233  $iQueued = 0;
234 
235  $oEmailTemplates = new ChWsbEmailTemplates();
236  $aSubscription = $this->getSubscription($aParams['unit'], $aParams['action']);
237 
238  if(!empty($aSubscription['params'])) {
239  $oFunction = function($arg1, $arg2, $arg3) use ($aSubscription) {
240  return eval($aSubscription['params']);
241  };
242 
243  $aUnitParams = $oFunction($aParams['unit'], $aParams['action'], $aParams['object_id']);
244  }
245 
246  if(isset($aUnitParams['skip']) && $aUnitParams['skip'] === true)
247  return $iQueued;
248 
249  $aSubscribers = $this->getAll("SELECT `id` AS `subscription_id`, `subscriber_id` AS `id`, `subscriber_type` AS `type` FROM `" . $this->_sPrefix . "entries` WHERE `subscription_id`='" . (empty($aSubscription['id']) ? 0 : $aSubscription['id']) . "'" . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . $aParams['object_id'] . "'" : ""));
250  foreach($aSubscribers as $aSubscriber) {
251  switch($aSubscriber['type']) {
253  $sSql = "SELECT '0' AS `id`, `name`, `email` FROM `" . $this->_sPrefix . "users` WHERE `id`= ? LIMIT 1";
254  break;
256  $sSql = "SELECT `ID` AS `id`, `NickName` AS `name`, `Email` AS `email` FROM `Profiles` WHERE `ID`= ? LIMIT 1";
257  break;
258  }
259  $aUser = $this->getRow($sSql, [$aSubscriber['id']]);
260 
261  //--- Parse message ---//
262  $sSql = "SELECT
263  `tse`.`id` AS `id`
264  FROM `" . $this->_sPrefix . "entries` AS `tse`
265  LEFT JOIN `" . $this->_sPrefix . "types` AS `tst` ON `tse`.`subscription_id`=`tst`.`id` AND `tst`.`unit`='" . $aParams['unit'] . "' AND `tst`.`action`<>''
266  WHERE `tse`.`subscriber_id`='" . $aSubscriber['id'] . "' AND `tse`.`subscriber_type`='" . $aSubscriber['type'] . "'" . ((int)$aParams['object_id'] != 0 ? " AND `object_id`='" . $aParams['object_id'] . "'" : "");
267  $aEntries = $this->getColumn($sSql);
268 
269  $aTemplateParams = array(
270  'RealName' => $aUser['id'] ? getNickName($aUser['id']) : $aUser['name'],
271  'Email' => $aUser['email'],
272  'ObjectId' => $aParams['object_id'],
273  'UnsubscribeLink' => $this->_oSubscription->_getUnsubscribeLink((int)$aSubscriber['subscription_id']),
274  'UnsubscribeAllLink' => $this->_oSubscription->_getUnsubscribeLink($aEntries),
275  );
276  if(isset($aUnitParams['template']))
277  $aTemplateParams = array_merge($aTemplateParams, $aUnitParams['template']);
278 
279  $aMail = $oEmailTemplates->parseTemplate($aSubscription['template'], $aTemplateParams, (int)$aUser['id']);
280 
281  $iQueued += (int)$this->query("INSERT INTO `" . $this->_sPrefix . "queue`(`email`, `subject`, `body`) VALUES('" . $aUser['email'] . "', '" . process_db_input($aMail['subject'], CH_TAGS_NO_ACTION, CH_SLASHES_NO_ACTION) . "', '" . process_db_input($aMail['body'], CH_TAGS_NO_ACTION, CH_SLASHES_NO_ACTION) . "')");
282  }
283 
284  return $iQueued;
285  }
286  function getSubscribersCount($iType)
287  {
288  switch($iType) {
290  $sSql = "SELECT
291  COUNT(DISTINCT `tsu`.`id`) AS `count`
292  FROM `" . $this->_sPrefix . "users` AS `tsu`
293  INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`id`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . CH_WSB_SBS_TYPE_VISITOR . "'
294  WHERE 1
295  LIMIT 1";
296  break;
298  $sSql = "SELECT
299  COUNT(DISTINCT `tsu`.`ID`) AS `count`
300  FROM `Profiles` AS `tsu`
301  INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`ID`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . CH_WSB_SBS_TYPE_MEMBER . "'
302  WHERE 1
303  LIMIT 1";
304  break;
305  }
306  return (int)$this->getOne($sSql);
307  }
308  function getSubscribers($iType, $iStart, $iCount)
309  {
310  switch($iType) {
312  $sSql = "SELECT
313  `tsu`.`id` AS `id`,
314  `tsu`.`name` AS `name`,
315  `tsu`.`email` AS `email`
316  FROM `" . $this->_sPrefix . "users` AS `tsu`
317  INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`id`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . CH_WSB_SBS_TYPE_VISITOR . "'
318  WHERE 1
319  GROUP BY `tsu`.`id`
320  LIMIT " . $iStart . "," . $iCount;
321  break;
323  $sSql = "SELECT
324  `tsu`.`ID` AS `id`,
325  `tsu`.`NickName` AS `name`,
326  `tsu`.`Email` AS `email`
327  FROM `Profiles` AS `tsu`
328  INNER JOIN `" . $this->_sPrefix . "entries` AS `tse` ON `tsu`.`ID`=`tse`.`subscriber_id` AND `tse`.`subscriber_type`='" . CH_WSB_SBS_TYPE_MEMBER . "'
329  WHERE 1
330  GROUP BY `tsu`.`ID`
331  LIMIT " . $iStart . "," . $iCount;
332  break;
333  }
334  return $this->getAll($sSql);
335  }
336 }
process_db_input
process_db_input($sText, $iStripTags=0)
Definition: utils.inc.php:256
ChWsbSubscriptionQuery\deleteSubscription
deleteSubscription($aParams)
Definition: ChWsbSubscriptionQuery.php:169
ChWsbSubscriptionQuery
Definition: ChWsbSubscriptionQuery.php:12
ChWsbEmailTemplates
Definition: ChWsbEmailTemplates.php:11
ch_import
ch_import($sClassName, $aModule=array())
Definition: utils.inc.php:1218
$aResult
$aResult
Definition: index.php:19
use
GNU LESSER GENERAL PUBLIC LICENSE February Free Software Inc Franklin Fifth MA USA Everyone is permitted to copy and distribute verbatim copies of this license but changing it is not allowed[This is the first released version of the Lesser GPL. It also counts as the successor of the GNU Library Public License, version 2, hence the version number 2.1.] Preamble The licenses for most software are designed to take away your freedom to share and change it By the GNU General Public Licenses are intended to guarantee your freedom to share and change free software to make sure the software is free for all its users This the Lesser General Public applies to some specially designated software packages typically libraries of the Free Software Foundation and other authors who decide to use it You can use it but we suggest you first think carefully about whether this license or the ordinary General Public License is the better strategy to use in any particular based on the explanations below When we speak of free we are referring to freedom of use
Definition: license.txt:27
ChWsbSubscriptionQuery\getSubscriptions
getSubscriptions($sUnit, $sAction='')
Definition: ChWsbSubscriptionQuery.php:62
sendMail
sendMail( $sRecipientEmail, $sMailSubject, $sMailBody, $iRecipientID=0, $aPlus=array(), $sEmailFlag='html', $isDisableAlert=false, $bForceSend=false)
Definition: utils.inc.php:461
ChWsbSubscriptionQuery\$_oSubscription
$_oSubscription
Definition: ChWsbSubscriptionQuery.php:14
ChWsbSubscriptionQuery\sendDelivery
sendDelivery($aParams)
Definition: ChWsbSubscriptionQuery.php:231
php
ChWsbDb\getAll
getAll($sQuery, $aBindings=[], $iFetchType=PDO::FETCH_ASSOC)
Definition: ChWsbDb.php:206
CH_SLASHES_NO_ACTION
const CH_SLASHES_NO_ACTION
Definition: utils.inc.php:30
ChWsbSubscriptionQuery\isSubscribed
isSubscribed($aParams)
Definition: ChWsbSubscriptionQuery.php:26
ChWsbDb\getRow
getRow($sQuery, $aBindings=[], $iFetchStyle=PDO::FETCH_ASSOC)
Definition: ChWsbDb.php:225
$aResults
$aResults
Definition: email_templates.php:25
ChWsbDb\query
query($sQuery, $aBindings=[])
Definition: ChWsbDb.php:386
getNickName
getNickName( $ID='')
Definition: profiles.inc.php:461
ChWsbSubscriptionQuery\$_sPrefix
$_sPrefix
Definition: ChWsbSubscriptionQuery.php:13
CH_TAGS_NO_ACTION
const CH_TAGS_NO_ACTION
Definition: utils.inc.php:21
_t
_t($key, $arg0="", $arg1="", $arg2="")
Definition: languages.inc.php:509
ChWsbSubscriptionQuery\getSubscribersCount
getSubscribersCount($iType)
Definition: ChWsbSubscriptionQuery.php:286
ChWsbSubscriptionQuery\getSubscription
getSubscription($sUnit, $sAction)
Definition: ChWsbSubscriptionQuery.php:49
ChWsbDb\getOne
getOne($sQuery, $aBindings=[], $iIndex=0)
Definition: ChWsbDb.php:263
ChWsbSubscriptionQuery\__construct
__construct(&$oSubscription)
Definition: ChWsbSubscriptionQuery.php:19
CH_TAGS_STRIP
const CH_TAGS_STRIP
Definition: utils.inc.php:22
ChWsbSubscriptionQuery\addSubscription
addSubscription($aParams)
Definition: ChWsbSubscriptionQuery.php:94
$aProfileInfo
$aProfileInfo
Definition: short_profile_info.php:23
$aUser
$aUser
Definition: profiles.inc.php:74
CH_WSB_SBS_TYPE_MEMBER
const CH_WSB_SBS_TYPE_MEMBER
Definition: ChWsbSubscription.php:11
getProfileInfo
getProfileInfo($iProfileID=0, $checkActiveStatus=false, $forceCache=false)
Definition: profiles.inc.php:249
ChWsbSubscriptionQuery\getSubscribers
getSubscribers($iType, $iStart, $iCount)
Definition: ChWsbSubscriptionQuery.php:308
CH_WSB_SBS_TYPE_VISITOR
const CH_WSB_SBS_TYPE_VISITOR
Definition: ChWsbSubscription.php:10
$sAction
$sAction
Definition: categories.php:274
ChWsbDb\lastId
lastId()
Definition: ChWsbDb.php:449
empty
Attr AllowedRel this is empty
Definition: Attr.AllowedRel.txt:7
ChWsbDb
Definition: ChWsbDb.php:13
as
as
Definition: Filter.ExtractStyleBlocks.Escaping.txt:10
ChWsbDb\getColumn
getColumn($sQuery, $aBindings=[])
Definition: ChWsbDb.php:243
$oSubscription
$oSubscription
Definition: notifies.php:28
ChWsbSubscriptionQuery\getSubscriptionsByUser
getSubscriptionsByUser($iUserId)
Definition: ChWsbSubscriptionQuery.php:74