WYSIJA_model_user

The MailPoet Newsletters WYSIJA model user class.

Defined (1)

The class is defined in the following location(s).

/models/user.php  
  1. class WYSIJA_model_user extends WYSIJA_model{ 
  2.  
  3. var $pk='user_id'; 
  4. var $table_name='user'; 
  5. var $columns=array( 
  6. 'user_id'=>array('auto'=>true),  
  7. 'wpuser_id' => array('req'=>true, 'type'=>'integer'),  
  8. 'email' => array('req'=>true, 'type'=>'email'),  
  9. 'firstname' => array(),  
  10. 'lastname' => array(),  
  11. 'ip' => array('req'=>true, 'type'=>'ip'),  
  12. 'keyuser' => array(),  
  13. 'status' => array('req'=>true, 'type'=>'boolean'),  
  14. 'created_at' => array('auto'=>true, 'type'=>'date'),  
  15. 'confirmed_ip' => array(),  
  16. 'confirmed_at' => array(),  
  17. 'last_opened' => array(),  
  18. 'last_clicked' => array(),  
  19. ); 
  20. var $searchable = array('email', 'firstname', 'lastname'); 
  21.  
  22. function __construct() { 
  23. $this->columns['status']['label']=__('Status', WYSIJA); 
  24. $this->columns['created_at']['label']=__('Created on', WYSIJA); 
  25. parent::__construct(); 
  26.  
  27. function refresh_columns() { 
  28. $WJ_Field = new WJ_Field(); 
  29. $custom_fields = $WJ_Field->get_all(); 
  30. if(!empty($custom_fields)) { 
  31. foreach($custom_fields as $row) { 
  32. $this->columns['cf_'.$row->id] = array(); 
  33.  
  34. function beforeInsert() { 
  35. // set the activation key 
  36. $model_user = WYSIJA::get( 'user' , 'model' ); 
  37.  
  38. $this->values['keyuser'] = md5( AUTH_KEY . $this->values['email'] . $this->values['created_at'] ); 
  39. while( $model_user->exists( array( 'keyuser' => $this->values['keyuser'] ) ) ) { 
  40. $this->values['keyuser'] = $this->generateKeyuser( $this->values['email'] ); 
  41. $model_user->reset(); 
  42.  
  43. if( !isset( $this->values['status'] ) ) { 
  44. $this->values['status'] = 0; 
  45.  
  46. // automatically add value to the field "domain". This is useful for statistics 
  47. $this->values['domain'] = substr( $this->values['email'] , strpos( $this->values['email'] , '@' ) +1 ); 
  48. return true; 
  49.  
  50. /** 
  51. * Get status number of a subscriber 
  52. * @param int $user_id 
  53. * @return int or null 
  54. */ 
  55. function getSubscriptionStatus($user_id) { 
  56. $this->getFormat = OBJECT; 
  57. $result = $this->getOne( array( 'status' ) , array( 'user_id' => $user_id ) ); 
  58. return ($result->status !== NULL) ? (int)$result->status : $result->status; 
  59.  
  60. /** 
  61. * count total subscribers per list based on parameters passed 
  62. * TODO we should use get_subscribers() instead 
  63. * @param array $list_ids 
  64. * @param boolean $confirmed_subscribers 
  65. * @return int 
  66. */ 
  67. function countSubscribers(Array $list_ids = array(), $confirmed_subscribers = true) 
  68. $model_config = WYSIJA::get('config', 'model'); 
  69. $confirm_dbleoptin = $model_config->getValue('confirm_dbleoptin'); 
  70. if($confirm_dbleoptin) $confirmed_subscribers = true; 
  71.  
  72.  
  73. $where = array(); 
  74. $where[] = 'C.is_enabled = 1'; 
  75. $where[] = $confirmed_subscribers ? 'status = 1' : 'status >= 0'; 
  76. if(!empty($list_ids)) { 
  77. $where[] = 'C.list_id IN ('.implode(', ', $list_ids).')'; 
  78.  
  79. $query = ' 
  80. SELECT 
  81. COUNT(DISTINCT A.user_id) 
  82. FROM 
  83. [wysija]user A 
  84. JOIN 
  85. [wysija]user_list B ON A.user_id = B.user_id 
  86. JOIN 
  87. [wysija]list C ON C.list_id = B.list_id 
  88. WHERE 1'; 
  89. if(!empty($where)) $query .= ' AND '.implode (' AND ', $where); 
  90. return $this->count($query); 
  91.  
  92.  
  93. /** 
  94. * get a user object not an array as we do by default with "getOne" 
  95. * @param int $user_id 
  96. * @return object 
  97. */ 
  98. function getObject($user_id) { 
  99. $this->getFormat=OBJECT; 
  100. return $this->getOne(false, array('user_id'=>$user_id)); 
  101.  
  102. /** 
  103. * Get User object using his email address 
  104. * @param string $email 
  105. * @return object WYSIJA_model_user 
  106. */ 
  107. function get_object_by_email($email) { 
  108. $this->getFormat=OBJECT; 
  109. return $this->getOne(false, array('email'=>$email)); 
  110.  
  111. /** 
  112. * get the details, lists and stats email counts regarding one user 
  113. * @param array $conditions : MailPoet's condition format 
  114. * @param boolean $stats : do we get the stats data of that user 
  115. * @param boolean $subscribed_list_only : 
  116. * @return boolean 
  117. */ 
  118. function getDetails($conditions, $stats=false, $subscribed_list_only=false) { 
  119. $data=array(); 
  120. $this->getFormat=ARRAY_A; 
  121. $array=$this->getOne(false, $conditions); 
  122. if(!$array) return false; 
  123.  
  124. $data['details'] = $array; 
  125.  
  126. //get the list that the user subscribed to 
  127. $model_user_list = WYSIJA::get('user_list', 'model'); 
  128. $conditions = array('user_id'=>$data['details']['user_id']); 
  129. if($subscribed_list_only) { 
  130. $conditions['unsub_date']=0; 
  131.  
  132. $data['lists'] = $model_user_list->get(false, $conditions); 
  133.  
  134. //get the user stats if requested 
  135. if($stats) { 
  136. $model_email_user_stat = WYSIJA::get('email_user_stat', 'model'); 
  137. $model_email_user_stat->setConditions(array('equal'=>array('user_id'=>$data['details']['user_id']))); 
  138. $data['emails'] = $model_email_user_stat->count(false); 
  139.  
  140. return $data; 
  141.  
  142. /** 
  143. * return the subscriber object for the currently logged in WordPress user 
  144. * @return object user 
  145. */ 
  146. function getCurrentSubscriber() { 
  147. static $result_user; 
  148. if(!empty($result_user)) return $result_user; 
  149. $this->getFormat = OBJECT; 
  150.  
  151. $wp_user_id = (int)WYSIJA::wp_get_userdata('ID'); 
  152. if( !( $wp_user_id > 0 ) ) { 
  153. return $result_user; 
  154. $result_user = $this->getOne(false, array('wpuser_id'=>$wp_user_id)); 
  155.  
  156. if(!$result_user) { 
  157. $this->getFormat = OBJECT; 
  158. $result_user = $this->getOne(false, array('email'=>WYSIJA::wp_get_userdata('user_email'))); 
  159. $this->update(array('wpuser_id'=>$wp_user_id), array('email'=>WYSIJA::wp_get_userdata('user_email'))); 
  160.  
  161. //the subscriber doesn't seem to exist let's insert it in the DB 
  162. if(!$result_user) { 
  163. $data = get_userdata($wp_user_id); 
  164. $firstname = $data->first_name; 
  165. $lastname = $data->last_name; 
  166. if(!$data->first_name && !$data->last_name) $firstname = $data->display_name; 
  167. $this->noCheck=true; 
  168. $this->insert(array( 
  169. 'wpuser_id'=>$data->ID,  
  170. 'email'=>$data->user_email,  
  171. 'firstname'=>$firstname,  
  172. 'lastname'=>$lastname)); 
  173.  
  174. $this->getFormat = OBJECT; 
  175. $result_user = $this->getOne(false, array('wpuser_id'=>$wp_user_id)); 
  176.  
  177. return $result_user; 
  178.  
  179. /** 
  180. * function used to generate the links for subscriber management, confirm, unsubscribe, edit subscription 
  181. * @param boolean, object $user_obj 
  182. * @param string $action what axctin will be performed (subscribe, unsubscribe, subscriptions) 
  183. * @param string $text name of the link 
  184. * @param boolean $url_only returns only the url no html wrapper 
  185. * @param string $target how does the link open 
  186. * @return type 
  187. */ 
  188. function getConfirmLink($user_obj = false, $action = 'subscribe', $text = false, $url_only = false, $target = '_blank' , $page_id_known = false) { 
  189. if(!$text) { 
  190. switch ($action) { 
  191. case 'unsubscribe': 
  192. $text = __('Click here to unsubscribe', WYSIJA); 
  193. break; 
  194.  
  195. case 'subscribe': 
  196. default: 
  197. $text = __('Click here to subscribe', WYSIJA); 
  198. break; 
  199.  
  200. $users_preview = false; 
  201. //if($action=='subscriptions')dbg($userObj); 
  202. if(!$user_obj) { 
  203. //preview mode 
  204. $user_obj = $this->getCurrentSubscriber(); 
  205. $users_preview = true; 
  206. $params = array( 
  207. 'wysija-page'=>1,  
  208. 'controller'=>'confirm',  
  209. ); 
  210. if($user_obj && isset($user_obj->keyuser)) { 
  211. //if the user key doesn exists let's generate it 
  212. if(!$user_obj->keyuser) { 
  213. $user_obj->keyuser = $this->generateKeyuser($user_obj->email); 
  214. while($this->exists(array('keyuser'=>$user_obj->keyuser))) { 
  215. $user_obj->keyuser = $this->generateKeyuser($user_obj->email); 
  216. $this->update(array('keyuser' => $user_obj->keyuser), array('user_id' => $user_obj->user_id)); 
  217.  
  218. $this->reset(); 
  219. $params['wysija-key'] = $user_obj->keyuser; 
  220. $params['action'] = $action; 
  221. $model_config = WYSIJA::get('config', 'model'); 
  222. if($users_preview) $params['demo']=1; 
  223.  
  224. $default_page_id = $model_config->getValue('confirm_email_link'); 
  225.  
  226. $action_page_param = array( 
  227. 'subscribe' => 'confirmation_page',  
  228. 'unsubscribe' => 'unsubscribe_page',  
  229. 'subscriptions' => 'subscriptions_page',  
  230. ); 
  231.  
  232. $page_id_for_action = $model_config->getValue($action_page_param[$action]); 
  233.  
  234. if(!$page_id_for_action || $default_page_id == $page_id_for_action) { 
  235. // if we use the default page created on install for this action 
  236. $page_id = $default_page_id; 
  237. }else{ 
  238. // if the page for this action has been modified in the settings 
  239. $page_id = $page_id_for_action; 
  240.  
  241. if($page_id_known!==false) $page_id = $page_id_known; 
  242.  
  243. $full_url = WYSIJA::get_permalink($page_id, $params); 
  244.  
  245. if($url_only) return $full_url; 
  246. return '<a href="'.$full_url.'" target="'.$target.'">'.$text.'</a>'; 
  247.  
  248. /** 
  249. * get the edit subscription link 
  250. * @param type $user_obj 
  251. * @param type $url_only 
  252. * @param type $target 
  253. * @return type 
  254. */ 
  255. function getEditsubLink($user_obj=false, $url_only=false, $target = '_blank') { 
  256. $model_config = WYSIJA::get('config', 'model'); 
  257.  
  258. return $this->getConfirmLink($user_obj, 'subscriptions', $model_config->getValue('manage_subscriptions_linkname'), $url_only, $target); 
  259.  
  260. /** 
  261. * get the unsubscribe link 
  262. * @param type $user_obj 
  263. * @param type $url_only 
  264. * @return string 
  265. */ 
  266. function getUnsubLink($user_obj=false, $url_only=false) { 
  267. $model_config = WYSIJA::get('config', 'model'); 
  268.  
  269. return $this->getConfirmLink($user_obj, 'unsubscribe', $model_config->getValue('unsubscribe_linkname'), $url_only); 
  270.  
  271. /** 
  272. * used to generate a hash to identify each subscriber, this is used later in confirmation links etc... 
  273. * @param string $email 
  274. * @return string md5 
  275. */ 
  276. function generateKeyuser($email) { 
  277. return md5( AUTH_KEY . $email . time() ); 
  278.  
  279. /** 
  280. * returns the user_id providing either the wpuser_id value or an email 
  281. * @param string $email 
  282. * @return int 
  283. */ 
  284. function user_id($email) { 
  285. $this->getFormat=ARRAY_A; 
  286. if(is_numeric($email)) { 
  287. $result = $this->getOne(array('user_id'), array('wpuser_id'=>$email)); 
  288. }else{ 
  289. $result = $this->getOne(array('user_id'), array('email'=>$email)); 
  290. return (int)$result['user_id']; 
  291.  
  292.  
  293. /** 
  294. * prepare the filters for a user select query based on the PHP global parameters 
  295. * @return array 
  296. */ 
  297. function detect_filters() { 
  298. $filters = array(); 
  299. // get the filters 
  300. if(!empty($_REQUEST['search'])) { 
  301. $filters['search'] = $_REQUEST['search']; 
  302.  
  303. if(!empty($_REQUEST['wysija']['filter']['search'])) { 
  304. $filters['search'] = $_REQUEST['wysija']['filter']['search']; 
  305.  
  306. // Lists filters 
  307. // Catch list filter from URL 
  308. if ( empty($_REQUEST['wysija']['filter']['filter_list']) && !empty($_REQUEST['filter-list']) ) { 
  309. if ($_REQUEST['filter-list'] == 'orphaned') { 
  310. $filters['lists'] = 0; // force to 0. 0 means: no list! 
  311. } else { 
  312. $filters['lists'] = $_REQUEST['filter-list']; 
  313.  
  314. if(!empty($_REQUEST['wysija']['filter']['filter_list'])) { 
  315. if ($_REQUEST['wysija']['filter']['filter_list'] == 'orphaned') { 
  316. $filters['lists'] = 0; // force to 0. 0 means: no list! 
  317. } else { 
  318. //we only get subscribed or unconfirmed users 
  319. $filters['lists'] = $_REQUEST['wysija']['filter']['filter_list']; 
  320.  
  321.  
  322. if(!empty($_REQUEST['link_filter'])) { 
  323. $filters['status'] = $_REQUEST['link_filter']; 
  324.  
  325. if(!empty($_REQUEST['wysija']['user']['timestamp'])) { 
  326. //$filters['created_at']= $_REQUEST['wysija']['user']['timestamp']; 
  327.  
  328. if (!empty($_REQUEST['action']) && $_REQUEST['action'] == 'actionvar_resendconfirmationemail') { 
  329. $filters['status'] = 'unconfirmed'; 
  330.  
  331.  
  332. return $filters; 
  333.  
  334.  
  335. /** 
  336. * count the confirmed and unconfirmed users for each list by status 
  337. * @param type $list_ids 
  338. * @return type 
  339. */ 
  340. function count_users_per_list($list_ids=array()) { 
  341. $select = array( 'COUNT(DISTINCT([wysija]user.user_id)) as total_users', '[wysija]user_list.list_id'); 
  342. $count_group_by = 'list_id'; 
  343.  
  344. // filters for unsubscribed 
  345. $filters = array(); 
  346. $filters['lists'] = $list_ids; 
  347. $filters['status'] = 'unsubscribed'; 
  348.  
  349. $unsubscribed_users = $this->get_subscribers( $select, $filters, $count_group_by ); 
  350.  
  351. $list_count_per_status=array(); 
  352. foreach($unsubscribed_users as $unsubscribed) { 
  353. if(!isset($list_count_per_status['list_id']['unsubscribers'])) { 
  354. $list_count_per_status[$unsubscribed['list_id']]['unsubscribers']=$unsubscribed['total_users']; 
  355.  
  356. // count confirmed subscribers 
  357. $filters = array(); 
  358. $filters['lists'] = $list_ids; 
  359. $filters['status'] = 'subscribed'; 
  360.  
  361. $subscribed_users = $this->get_subscribers( $select, $filters, $count_group_by ); 
  362.  
  363. foreach($subscribed_users as $subscribed) { 
  364. if(!isset($list_count_per_status['list_id']['subscribers'])) { 
  365. $list_count_per_status[$subscribed['list_id']]['subscribers']=$subscribed['total_users']; 
  366.  
  367. // count unconfirmed subscribers 
  368. $filters = array(); 
  369. $filters['lists'] = $list_ids; 
  370. $filters['status'] = 'unconfirmed'; 
  371.  
  372. $unconfirmed_users = $this->get_subscribers( $select, $filters, $count_group_by); 
  373.  
  374. foreach($unconfirmed_users as $unconfirmed) { 
  375. if(!isset($list_count_per_status['list_id']['unconfirmed'])) { 
  376. $list_count_per_status[$unconfirmed['list_id']]['unconfirmed']=$unconfirmed['total_users']; 
  377.  
  378. // get the total count of subscribers per list 
  379. $filters = array(); 
  380. $filters['lists'] = $list_ids; 
  381. $total_belonging = $this->get_subscribers( $select, $filters , $count_group_by ); 
  382.  
  383. // get the count of confirmed user per each and unconfirmed user per list 
  384. foreach($total_belonging as $belonging) { 
  385. if(!isset($list_count_per_status['list_id']['belonging'])) { 
  386. $list_count_per_status[$belonging['list_id']]['belonging']=$belonging['total_users']; 
  387.  
  388. return $list_count_per_status; 
  389.  
  390.  
  391. function _convert_filters($filters_in) { 
  392. $filters_out = array(); 
  393. $model_config = WYSIJA::get('config', 'model'); 
  394. $filter_has_list = false; 
  395.  
  396. // here we found a search condition 
  397. if(!empty($filters_in['search'])) { 
  398. $filters_out['like'] = array(); 
  399. $filters_in['search'] = trim($filters_in['search']); 
  400. foreach($this->searchable as $field) { 
  401. $filters_out['like'][$field] = trim($filters_in['search']); 
  402.  
  403. // as soon as we detect lists we set the query that way 
  404. if (isset($filters_in['lists']) && $filters_in['lists'] === 0) // orphan 
  405. $filters_out['equal']['list_id'] = 0; 
  406. }elseif(!empty($filters_in['lists'])) { 
  407. $filters_out['equal']['list_id'] = $filters_in['lists']; 
  408. $filter_has_list = true; 
  409.  
  410. // we detect a status condition 
  411. if(!empty($filters_in['status'])) { 
  412. switch($filters_in['status']) { 
  413. case 'unconfirmed': 
  414. $filters_out['equal']['status'] = 0; 
  415.  
  416. if($filter_has_list) { 
  417. $filters_out['greater_eq']['sub_date'] =0; 
  418. $filters_out['equal']['unsub_date'] =0; 
  419. break; 
  420. case 'unsubscribed': 
  421. $filters_out['equal']['status'] = -1; 
  422.  
  423. if($filter_has_list) { 
  424. $filters_out['equal']['sub_date'] =0; 
  425. $filters_out['greater_eq']['unsub_date'] =0; 
  426. break; 
  427. case 'subscribed': 
  428. if($model_config->getValue('confirm_dbleoptin')) $filters_out['equal']['status'] = 1; 
  429. else $filters_out['greater_eq']=array('status'=>0); 
  430.  
  431. if($filter_has_list) { 
  432. $filters_out['greater_eq']['sub_date'] =0; 
  433. $filters_out['equal']['unsub_date'] =0; 
  434. break; 
  435.  
  436. case 'inactive': 
  437. $temporary_table = $this->get_inactive_subscribers_table(); 
  438. // A in "A.user_id" is correct but it's not recommended. 
  439. $filters_out['is']['A.user_id'] = 'IN (SELECT inactive_users.`user_id` FROM `'.$temporary_table.'` inactive_users)'; 
  440. break; 
  441.  
  442. case 'all': 
  443. break; 
  444.  
  445. if(!empty($filters_in['created_at'])) { 
  446. $filters_out['less_eq']['created_at'] = $filters_in['created_at']; 
  447.  
  448.  
  449. return $filters_out; 
  450. /** 
  451. * @param type $select 
  452. * @param type $filters 
  453. */ 
  454. function get_subscribers($select = array(), $filters = array(), $count_group_by = '', $return_query=false) { 
  455.  
  456. $this->noCheck=true; 
  457. $is_count = false; 
  458.  
  459. $select = str_replace(array('[wysija]user_list', '[wysija]user', 'count('), array('B', 'A', 'COUNT('), $select); 
  460.  
  461. if(!empty($filters)) { 
  462. $filters = $this->_convert_filters($filters); 
  463. $this->setConditions($filters); 
  464.  
  465.  
  466.  
  467. //1 - prepare select 
  468. if(isset($filters['equal']['list_id'])) { 
  469. // orphans are selected with this kind of join 
  470. if($filters['equal']['list_id']=== 0) { 
  471. // reset all prefixes. We are selecting from only 1 table - [wysija]user 
  472. $select_string = implode(', ', $select); 
  473.  
  474. // make sure we select the user_id from the table that has that information not from user_list which will return NULL 
  475. $select_string = str_replace('B.user_id', 'A.user_id', $select_string); 
  476.  
  477. // we need to make the difference between the count query useful for pagination etc and the rest 
  478. $is_count = strpos($select_string, 'COUNT(') !== false; 
  479.  
  480. // this query left joins on null values of user_list, allows us to display the subscribers not belonging to any list 
  481. $query = 'SELECT '.$select_string.' 
  482. FROM [wysija]user as A 
  483. LEFT OUTER JOIN [wysija]user_list as B on A.user_id = B.user_id 
  484. WHERE B.`user_id` is NULL'; 
  485.  
  486. $this->conditions=array(); // reset all conditions 
  487. $filters = array(); // reset all conditions 
  488.  
  489. }else{ 
  490. // standard select when lists ids are in the filters 
  491.  
  492. $select_string = implode(', ', $select); 
  493. if(strpos($select_string, 'COUNT(') === false) { 
  494. $select_string = str_replace('A.user_id', 'DISTINCT(B.user_id)', $select_string); 
  495. }else{ 
  496. $is_count = true; 
  497.  
  498. $query = 'SELECT '.$select_string.' FROM `[wysija]user_list` as B'; 
  499. $query .= ' JOIN `[wysija]user` as A on A.user_id=B.user_id'; 
  500. } else { 
  501. // when there is no filter list 
  502. $select_string = implode(', ', $select); 
  503. if(strpos($select_string, 'COUNT(') === false) { 
  504. $select_string = str_replace('B.user_id', 'A.user_id', $select_string); 
  505. }else{ 
  506. $is_count = true; 
  507.  
  508. $query = 'SELECT '.$select_string.' FROM `[wysija]user` as A'; 
  509.  
  510. $query .= $this->makeWhere(); 
  511.  
  512. if(!$is_count) { 
  513. if($return_query) return $query; 
  514.  
  515. if( empty($_REQUEST['orderby']) || !is_string($_REQUEST['orderby']) || preg_match('|[^a-z0-9#_.-]|i', $_REQUEST['orderby']) !== 0 ) { 
  516. if(!empty($_REQUEST['wysija']['filter']['filter_list']) && $_REQUEST['wysija']['filter']['filter_list'] == 'orphaned') { 
  517. $order_by = ''; 
  518. }else{ 
  519. $order_by = ' ORDER BY A.user_id DESC'; 
  520. }else{ 
  521.  
  522. if(!in_array(strtoupper($_REQUEST['ordert']), array('DESC', 'ASC'))) { 
  523. $_REQUEST['ordert'] = 'DESC'; 
  524.  
  525. $order_by = ' ORDER BY `'.$_REQUEST['orderby'].'` '.$_REQUEST['ordert']; 
  526.  
  527.  
  528.  
  529.  
  530. $query = $query.' '.$order_by.$this->setLimit(); 
  531. return $this->getResults($query); 
  532. }else{ 
  533. if(!empty($count_group_by)) { 
  534. return $this->getResults($query.' GROUP BY '.$count_group_by); 
  535. }else{ 
  536. $result = $this->getResults($query); 
  537. return $result[0]; 
  538.  
  539. /** 
  540. * Add last opens of one or more users 
  541. * @todo: Not in use. Will be removed soon 
  542. * @param array $users 
  543. * @return array 
  544. * Array( 
  545. * int => array( 
  546. * 'user_id' => int,  
  547. * 'firstname' => string,  
  548. * 'lastname' => string,  
  549. * ... 
  550. * 'last_open' => int 
  551. * ) 
  552. * ) 
  553. */ 
  554. protected function add_last_opens(Array $users) { 
  555. $user_ids = array(); 
  556. foreach ($users as $subscriber) { 
  557. $user_ids[] = $subscriber['user_id']; 
  558. $query = ' 
  559. SELECT 
  560. eus.`user_id`,  
  561. MAX(eus.`opened_at`) AS `last_open` 
  562. FROM 
  563. [wysija]email_user_stat eus 
  564. WHERE 
  565. eus.`user_id` IN ('.implode(', ', $user_ids).') 
  566. GROUP BY 
  567. eus.`user_id` 
  568. '; 
  569. $users_last_opens = $this->indexing_dataset_by_field('user_id', $this->getResults($query), true, 'last_open'); 
  570. foreach ($users as &$user) { 
  571. $user['last_open'] = !empty($users_last_opens[$user['user_id']]) ? $users_last_opens[$user['user_id']] : null; 
  572. return $users; 
  573.  
  574. /** 
  575. * Add last opens of one or more users 
  576. * @todo: Not in use. Will be removed soon 
  577. * @param array $users 
  578. * @return array 
  579. * Array( 
  580. * int => array( 
  581. * 'user_id' => int,  
  582. * 'firstname' => string,  
  583. * 'lastname' => string,  
  584. * ... 
  585. * 'last_click' => int 
  586. * ) 
  587. * ) 
  588. */ 
  589. protected function add_last_clicks(Array $users) { 
  590. $user_ids = array(); 
  591. foreach ($users as $subscriber) { 
  592. $user_ids[] = $subscriber['user_id']; 
  593. $query = ' 
  594. SELECT 
  595. euu.`user_id`,  
  596. MAX(euu.`clicked_at`) AS `last_click` 
  597. FROM 
  598. [wysija]email_user_url euu 
  599. WHERE 
  600. euu.`user_id` IN ('.implode(', ', $user_ids).') 
  601. GROUP BY 
  602. euu.`user_id` 
  603. '; 
  604. $users_last_clicks = $this->indexing_dataset_by_field('user_id', $this->getResults($query), true, 'last_click'); 
  605. foreach ($users as &$user) { 
  606. $user['last_click'] = !empty($users_last_clicks[$user['user_id']]) ? $users_last_clicks[$user['user_id']] : null; 
  607. return $users; 
  608.  
  609.  
  610. protected static $_inactive_subscribers_table = null; 
  611. /** 
  612. * Get name of a temporary table of inactive users. It's helpful for all actions related to MASSIVE editing subscribers 
  613. * @return type 
  614. */ 
  615. protected function get_inactive_subscribers_table() { 
  616. if (empty(self::$_inactive_subscribers_table)) 
  617. self::$_inactive_subscribers_table = '[wysija]is'.time(); 
  618. return self::$_inactive_subscribers_table; 
  619.  
  620. /** 
  621. * Preprare a temporary table of inactive users. It's helpful for all actions related to MASSIVE editing subscribers 
  622. */ 
  623. public function prepare_inactive_users_table() { 
  624. // Use temporary table for better joins 
  625. $temporary_table = $this->get_inactive_subscribers_table(); 
  626. $temporary_table_2 = $temporary_table.'_2'; 
  627. $queries = array(); 
  628. // Main table which stores ID of inactive subscribers 
  629. $queries[] = ' 
  630. CREATE TEMPORARY TABLE `'.$temporary_table.'` ( 
  631. `user_id` INT(10) NOT NULL,  
  632. `created_at` INT(10) NULL,  
  633. PRIMARY KEY(`user_id`) 
  634. '; 
  635. $queries[] = ' 
  636. CREATE TEMPORARY TABLE `'.$temporary_table_2.'` ( 
  637. `user_id` INT(10) NOT NULL,  
  638. PRIMARY KEY(`user_id`) 
  639. '; 
  640. $queries[] = ' 
  641. INSERT INTO `'.$temporary_table_2.'` 
  642. SELECT 
  643. DISTINCT `user_id` 
  644. FROM 
  645. `[wysija]email_user_stat` 
  646. GROUP BY 
  647. `user_id` 
  648. HAVING SUM(IF (`status` < 0, 0, `status`)) <= 0 
  649. '; 
  650. $queries[] = ' 
  651. INSERT INTO `'.$temporary_table.'` 
  652. SELECT 
  653. DISTINCT u.`user_id`, u.`created_at` 
  654. FROM 
  655. `'.$temporary_table_2.'` t2 
  656. LEFT JOIN 
  657. `[wysija]user` u ON t2.`user_id` = u.`user_id` 
  658. WHERE 
  659. u.`status` > 0 
  660. GROUP BY 
  661. u.`user_id` 
  662. '; 
  663. foreach ($queries as $query) { 
  664. $this->getResults($query); 
  665. /** 
  666. * Count inactive users. 
  667. * Inactive users are people who: 
  668. * - AND were confirmed (status > 0) 
  669. * - AND were received at least 1 newsletter 
  670. * - AND have never opened or clicked 
  671. * @return array 
  672. * Array ( 
  673. * 'count' => int,  
  674. * 'max_created_at' => int 
  675. * ) 
  676. */ 
  677. public function count_inactive_users() { 
  678. $temporary_table = $this->get_inactive_subscribers_table(); 
  679. $main_query = 'SELECT COUNT(*) AS `count`, MAX(`created_at`) AS `max_created_at` FROM `'.$temporary_table.'` '; 
  680. $result = $this->getResults($main_query); 
  681. if ($result) 
  682. return $result[0]; 
  683. public function structure_user_status_count_array($count_by_status) { 
  684. $counts = array( 
  685. 'unsubscribed' => 0,  
  686. 'unconfirmed' => 0,  
  687. 'subscribed' => 0,  
  688. 'inactive' => 0 
  689. ); 
  690. $model_config = WYSIJA::get('config', 'model'); 
  691. $is_dbleoptin = (boolean)$model_config->getValue('confirm_dbleoptin'); 
  692.  
  693. foreach ($count_by_status as $status_data) { 
  694. switch ($status_data['status']) { 
  695. case '-1': 
  696. $counts['unsubscribed'] += $status_data['users']; 
  697. break; 
  698.  
  699. case '0': 
  700. if ($is_dbleoptin) { 
  701. $counts['unconfirmed'] += $status_data['users']; 
  702. } else { 
  703. $counts['subscribed'] += $status_data['users']; 
  704. break; 
  705.  
  706. case '1': 
  707. $counts['subscribed'] += $status_data['users']; 
  708. break; 
  709.  
  710. case '-99': 
  711. $counts['inactive'] += $status_data['users']; 
  712. break; 
  713.  
  714. default: 
  715. break; 
  716.  
  717. $counts['all'] = array_sum(array_values($counts)) - $counts['inactive']; 
  718. return $counts; 
  719.  
  720. public function get_max_create($count_by_status) { 
  721. $arr_max_create_at = array(); 
  722. foreach($count_by_status as $status_data) { 
  723. $arr_max_create_at[] = $status_data['max_create_at']; 
  724. return $arr_max_create_at; 
  725.  
  726.  
  727. /** 
  728. * triggered before a user is deleted using the delete function of the user model 
  729. * @return boolean 
  730. */ 
  731. function beforeDelete($conditions) { 
  732. $model_user = new WYSIJA_model_user(); 
  733. $users = $model_user->get(array('user_id'), $this->conditions); 
  734. $user_ids = array(); 
  735. foreach($users as $user) $user_ids[]=$user['user_id']; 
  736.  
  737. //delete all the user stats 
  738. $model_email_user_stat=WYSIJA::get('email_user_stat', 'model'); 
  739. $conditions=array('user_id'=>$user_ids); 
  740. $model_email_user_stat->delete($conditions); 
  741. //delete all the queued emails 
  742. $model_queue=WYSIJA::get('queue', 'model'); 
  743. $model_queue->delete($conditions); 
  744. return true; 
  745.  
  746. /** 
  747. * triggered after a user is deleted using the delete function of the user model 
  748. * @return boolean 
  749. */ 
  750. function afterDelete() { 
  751. $helper_user=WYSIJA::get('user', 'helper'); 
  752. $helper_user->refreshUsers(); 
  753. return true; 
  754.  
  755. /** 
  756. * triggered after a user is inserted using the insert function of the user model 
  757. * @return boolean 
  758. */ 
  759. function afterInsert($id) { 
  760. $helper_user=WYSIJA::get('user', 'helper'); 
  761. $helper_user->refreshUsers(); 
  762.  
  763. $this->_update_domain_field(); 
  764.  
  765. do_action('wysija_subscriber_added', $id); 
  766. return true; 
  767.  
  768.  
  769.  
  770. function beforeUpdate($id=null) { 
  771.  
  772. do_action('wysija_subscriber_modified', $id); 
  773. return true; 
  774.  
  775. function _update_domain_field() { 
  776. // automatically add value to the field "domain". This is useful for statistics 
  777. // but only possible if we update the email 
  778. if(isset($this->values['email'])) { 
  779. $this->values['domain'] = substr( 
  780. $this->values['email'] ,  
  781. strpos($this->values['email'],  
  782. '@')+1); 
  783.  
  784. function afterUpdate($id) { 
  785. $helper_user=WYSIJA::get('user', 'helper'); 
  786. $helper_user->refreshUsers(); 
  787.  
  788. $this->_update_domain_field(); 
  789.  
  790. do_action('wysija_subscriber_modified', $id); 
  791. return true; 
  792.  
  793.  
  794. /** 
  795. * function used to generate the links if they are not valid anymore,  
  796. * will be needed for old version of the plugin still using old unsafe links 
  797. * @param int $user_id 
  798. * @param int $email_id 
  799. * @return string 
  800. */ 
  801. function old_get_new_link_for_expired_links($user_id, $email_id) { 
  802. $params=array( 
  803. 'wysija-page'=>1,  
  804. 'controller'=>'confirm',  
  805. 'action'=>'resend',  
  806. 'user_id'=>$user_id,  
  807. 'email_id'=>$email_id 
  808. ); 
  809.  
  810. $model_config = WYSIJA::get('config', 'model'); 
  811. return WYSIJA::get_permalink($model_config->getValue('confirm_email_link'), $params);