WYSIJA_model_queue

The MailPoet Newsletters WYSIJA model queue class.

Defined (1)

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

/models/queue.php  
  1. class WYSIJA_model_queue extends WYSIJA_model{ 
  2.  
  3. var $pk=array('email_id', 'user_id'); 
  4. var $table_name='queue'; 
  5. var $columns=array( 
  6. 'email_id'=>array('type'=>'integer'),  
  7. 'user_id'=>array('type'=>'integer'),  
  8. 'send_at' => array('req'=>true, 'type'=>'integer'),  
  9. 'priority' => array('type'=>'integer'),  
  10. 'number_try' => array('type'=>'integer') 
  11. ); 
  12.  
  13. function __construct() { 
  14. parent::__construct(); 
  15.  
  16. /** 
  17. * used to put emails in the queue when starting to send or adding user to a follow_up email 
  18. * @param array $email 
  19. * @return boolean 
  20. */ 
  21. function queue_email($email) { 
  22. // make sure that the email array has at least those 3 important parameters set 
  23. if(!isset($email['email_id']) || !isset($email['type']) || !isset($email['params'])) { 
  24. $this->error('Missing data to queue email'); 
  25. return false; 
  26.  
  27. $follow_up = $emails_need_to_be_queued = false; 
  28. // we cannot queue all kinds of emails make sure this email can be queued 
  29. if((int)$email['type'] === 2) { 
  30. // if we are in a subscriber follow-up case then we can queue emails of the list 
  31. // only if it's the first time that we hit the send button 
  32. if(isset($email['params']) && $email['params']['autonl']['event']=='subs-2-nl' && (int)$email['sent_at']===0) { 
  33. $emails_need_to_be_queued=true; 
  34. $follow_up=true; 
  35.  
  36. }else{ 
  37. // queue emails only if it is not a scheduled email 
  38. if(!isset($email['params']['schedule']['isscheduled'])) { 
  39. $emails_need_to_be_queued = true; 
  40. }else{ 
  41. $helper_toolbox = WYSIJA::get('toolbox', 'helper'); 
  42. $schedule_date = $email['params']['schedule']['day'].' '.$email['params']['schedule']['time']; 
  43. $unix_scheduled_time = strtotime($schedule_date); 
  44.  
  45. // if the scheduled time is passed let's send the email 
  46. // we don't compare to the time recorded but to the offset time which is the time set by the user in his time 
  47. if($helper_toolbox->localtime_to_servertime($unix_scheduled_time) < time()) { 
  48. $emails_need_to_be_queued = true; 
  49.  
  50. // we're not supposed to queue any email here probably because it is an automatic newsletter or a scheduled email 
  51. if(!$emails_need_to_be_queued) return false; 
  52.  
  53.  
  54. // if it is a standard email we get the campaign list 
  55. if(!$follow_up) { 
  56. $model_campaign = WYSIJA::get('campaign', 'model'); 
  57. $data = $model_campaign->getDetails($email['email_id']); 
  58.  
  59. $lists_to_send_to = $data['campaign']['lists']['ids']; 
  60. $to_be_sent=time(); 
  61. }else{ 
  62. // if it is a follow up we get the campaign list 
  63. $lists_to_send_to = array($email['params']['autonl']['subscribetolist']); 
  64. $delay = $this->calculate_delay($email['params']['autonl']); 
  65. $to_be_sent = '(A.sub_date) + '.$delay; 
  66.  
  67. // get the minimum status to queue emails based on the double optin config 
  68. $model_config=WYSIJA::get('config', 'model'); 
  69. if($model_config->getValue('confirm_dbleoptin')) $status_min=0; 
  70. else $status_min=-1; 
  71.  
  72. if(empty($lists_to_send_to)) { 
  73. $this->error(__('There are no list to send to.', WYSIJA), 1); 
  74. return false; 
  75. // insert into the queue 
  76. $query='INSERT IGNORE INTO [wysija]queue (`email_id` , `user_id`, `send_at`) '; 
  77. $query.='SELECT '.$email['email_id'].', A.user_id, '.$to_be_sent.' 
  78. FROM [wysija]user_list as A 
  79. JOIN [wysija]user as B on A.user_id=B.user_id 
  80. WHERE B.status>'.$status_min.' 
  81. AND A.list_id IN ('.implode(', ', $lists_to_send_to).') 
  82. AND A.unsub_date=0'; 
  83.  
  84. // if some emails have already been sent on that newsletter, make sure we don't re enqueue the same emails again 
  85. $query_count = 'SELECT count(user_id) as count FROM [wysija]email_user_stat WHERE email_id = '.$email['email_id']; 
  86. if( $this->count( $query_count) > 0) { 
  87. $query .= ' AND A.user_id NOT IN (SELECT user_id FROM [wysija]email_user_stat WHERE email_id = '.$email['email_id'].')'; 
  88.  
  89. $this->query($query); 
  90.  
  91. if($this->sql_error) { 
  92.  
  93. $this->error($this->sql_error); 
  94. $this->error('Full query : '.$query); 
  95. return false; 
  96. }else{ 
  97. // rows were inserted 
  98. $nb_emails=$this->getAffectedRows(); 
  99. if((int)$nb_emails > 0) { 
  100. //$this->notice(sprintf(__('%1$s email(s) queued', WYSIJA), $nb_emails), false); 
  101. return true; 
  102. return true; 
  103.  
  104.  
  105. /** 
  106. * get a list of the delaied queued emails 
  107. * @param type $mailid 
  108. * @return type 
  109. */ 
  110. function getDelayed($mailid=0) { 
  111. if(!$mailid) return array(); 
  112. $query = 'SELECT c.*, a.* FROM [wysija]queue as a'; 
  113. $query .= ' JOIN [wysija]email as b on a.`email_id` = b.`email_id` '; 
  114. $query .= ' JOIN [wysija]user as c on a.`user_id` = c.`user_id` '; 
  115. $query .= ' WHERE b.`status` IN (1, 3, 99)'; 
  116. if(!empty($mailid)) $query .= ' AND a.`email_id` = '.$mailid; 
  117. $query .= ' ORDER BY a.`priority` ASC, a.`send_at` ASC, a.`user_id` ASC'; 
  118.  
  119. $results=$this->query('get_res', $query); 
  120.  
  121.  
  122. return $results; 
  123.  
  124. /** 
  125. * get a list of the emails ready to be sent 
  126. * @param string $sql_limit 
  127. * @param int $email_id 
  128. * @param int $user_id 
  129. * @return type 
  130. */ 
  131. function getReady($sql_limit, $email_id = 0, $user_id=false) { 
  132. // in some cases of a large database, with millions of entries recorded in the queue table, the triple joins was 
  133. // very slow and crashing the request. 
  134. $model_config = WYSIJA::get('config', 'model'); 
  135. if ((int) $model_config->getValue('total_subscribers') > 1000000) { 
  136. return $this->get_ready_large_db($sql_limit, $email_id = 0, $user_id=false); 
  137.  
  138. $query = 'SELECT c.*, a.* FROM [wysija]queue as a'; 
  139. $query .= ' JOIN [wysija]email as b on a.`email_id` = b.`email_id` '; 
  140. $query .= ' JOIN [wysija]user as c on a.`user_id` = c.`user_id` '; 
  141. $query .= ' WHERE a.`send_at` <= '.time().' AND b.`status` IN (1, 3, 99)'; 
  142. if(!empty($email_id)) $query .= ' AND a.`email_id` = '.$email_id; 
  143. if($user_id) $query .= ' AND a.`user_id` = '.$user_id; 
  144. $query .= ' ORDER BY a.`priority` ASC, a.`send_at` ASC, a.`user_id` ASC'; 
  145. if(!empty($sql_limit)) $query .= ' LIMIT '.$sql_limit; 
  146.  
  147. $results=$this->query('get_res', $query, OBJECT_K); 
  148. if($results === null) { 
  149. $this->query('REPAIR TABLE [wysija]queue, [wysija]user, [wysija]email'); 
  150.  
  151. if(!empty($results)) { 
  152. $first_element_queued = reset($results); 
  153. $this->query('UPDATE [wysija]queue SET send_at = send_at + 1 WHERE email_id = '.$first_element_queued->email_id.' AND user_id = '.$first_element_queued->user_id.' LIMIT 1'); 
  154. return $results; 
  155.  
  156. /** 
  157. * this function is here to make sure that if the subscribers has a large db, and the queue has millions of entry, the 
  158. * selection request will still work 
  159. * @param type $sql_limit 
  160. * @param type $email_id 
  161. * @param type $user_id 
  162. * @return type 
  163. */ 
  164. function get_ready_large_db($sql_limit, $email_id = 0, $user_id=false) { 
  165.  
  166. $query = 'SELECT A.user_id, A.email_id 
  167. FROM [wysija]queue AS A 
  168. JOIN [wysija]email AS B ON A.`email_id` = B.`email_id` 
  169. WHERE A.`send_at` <= '.time().' AND B.status IN ( 1, 3, 99 ) '; 
  170. if($user_id) $query .= ' AND A.`user_id` = '.$user_id; 
  171. if(!empty($email_id)) $query .= ' AND A.`email_id` = '.$email_id; 
  172. $query .= ' ORDER BY A.`priority` ASC , A.`send_at` ASC , A.`user_id` ASC'; 
  173.  
  174. if(!empty($sql_limit)) $query .= ' LIMIT '.$sql_limit; 
  175.  
  176. // the first request is looking for data between the queue table and the email table 
  177. $results = $this->query('get_res', $query, OBJECT_K); 
  178. if(empty($results)) return $result; 
  179. $user_ids = $email_ids = ''; 
  180. $email_ids = array(); 
  181.  
  182. foreach($results as $ids) { 
  183. $user_ids .= $ids->user_id. ', '; 
  184. $email_ids[$ids->email_id] = $ids->email_id ; 
  185.  
  186. $user_ids = substr($user_ids, 0, -1); 
  187.  
  188. $email_ids_string = implode(', ', $email_ids); 
  189.  
  190. if(substr($email_ids_string, 0, -1) == ', ') $email_ids_string = substr($email_ids_string, 0, -1); 
  191.  
  192.  
  193. $query = 'SELECT c.*, a.* 
  194. FROM [wysija]queue as a 
  195. JOIN [wysija]user as c on a.`user_id` = c.`user_id` 
  196. WHERE a.user_id IN('.$user_ids.') and a.email_id IN('.$email_ids_string.') ORDER BY a.`priority` ASC, a.`send_at` ASC, a.`user_id`'; 
  197.  
  198. $results=$this->query('get_res', $query, OBJECT_K); 
  199. if($results === null) { 
  200. $this->query('REPAIR TABLE [wysija]queue, [wysija]user, [wysija]email'); 
  201.  
  202. if(!empty($results)) { 
  203. $first_element_queued = reset($results); 
  204. $this->query('UPDATE [wysija]queue SET send_at = send_at + 1 WHERE email_id = '.$first_element_queued->email_id.' AND user_id = '.$first_element_queued->user_id.' LIMIT 1'); 
  205. return $results; 
  206.  
  207. /** 
  208. * calculate the delay of the follow up based on the email parameters that have been setup 
  209. * @param array $email_params_autonl 
  210. * @return int 
  211. */ 
  212. function calculate_delay($email_params_autonl) { 
  213. $delay=0; 
  214.  
  215. //check if there is a delay, if so we just set a send_at params 
  216. if(isset($email_params_autonl['numberafter']) && (int)$email_params_autonl['numberafter']>0) { 
  217. switch($email_params_autonl['numberofwhat']) { 
  218. case 'immediate': 
  219. $delay=0; 
  220. break; 
  221. case 'hours': 
  222. $delay=(int)$email_params_autonl['numberafter']*3600; 
  223. break; 
  224. case 'days': 
  225. $delay=(int)$email_params_autonl['numberafter']*3600*24; 
  226. break; 
  227. case 'weeks': 
  228. $delay=(int)$email_params_autonl['numberafter']*3600*24*7; 
  229. break; 
  230. return $delay; 
  231.