public function testcreate_queue_items_query() { $emailMan = new EmailMan(); //without parameters $expected = "SELECT emailman.* ,\n\t\t\t\t\tcampaigns.name as campaign_name,\n\t\t\t\t\temail_marketing.name as message_name,\n\t\t\t\t\t(CASE related_type\n\t\t\t\t\t\tWHEN 'Contacts' THEN LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,''))))\n\t\t\t\t\t\tWHEN 'Leads' THEN LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,''))))\n\t\t\t\t\t\tWHEN 'Accounts' THEN accounts.name\n\t\t\t\t\t\tWHEN 'Users' THEN LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,''))))\n\t\t\t\t\t\tWHEN 'Prospects' THEN LTRIM(RTRIM(CONCAT(IFNULL(prospects.first_name,''),' ',IFNULL(prospects.last_name,''))))\n\t\t\t\t\tEND) recipient_name FROM emailman\n\t\t LEFT JOIN users ON users.id = emailman.related_id and emailman.related_type ='Users'\n\t\t\t\t\tLEFT JOIN contacts ON contacts.id = emailman.related_id and emailman.related_type ='Contacts'\n\t\t\t\t\tLEFT JOIN leads ON leads.id = emailman.related_id and emailman.related_type ='Leads'\n\t\t\t\t\tLEFT JOIN accounts ON accounts.id = emailman.related_id and emailman.related_type ='Accounts'\n\t\t\t\t\tLEFT JOIN prospects ON prospects.id = emailman.related_id and emailman.related_type ='Prospects'\n\t\t\t\t\tLEFT JOIN prospect_lists ON prospect_lists.id = emailman.list_id\n LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = emailman.related_id and emailman.related_type = email_addr_bean_rel.bean_module and email_addr_bean_rel.primary_address = 1 and email_addr_bean_rel.deleted=0\n\t\t\t\t\tLEFT JOIN campaigns ON campaigns.id = emailman.campaign_id\n\t\t\t\t\tLEFT JOIN email_marketing ON email_marketing.id = emailman.marketing_id WHERE emailman.deleted=0"; $actual = $emailMan->create_queue_items_query('', ''); $this->assertSame($expected, $actual); //with parameters $expected = "SELECT emailman.* ,\n\t\t\t\t\tcampaigns.name as campaign_name,\n\t\t\t\t\temail_marketing.name as message_name,\n\t\t\t\t\t(CASE related_type\n\t\t\t\t\t\tWHEN 'Contacts' THEN LTRIM(RTRIM(CONCAT(IFNULL(contacts.first_name,''),' ',IFNULL(contacts.last_name,''))))\n\t\t\t\t\t\tWHEN 'Leads' THEN LTRIM(RTRIM(CONCAT(IFNULL(leads.first_name,''),' ',IFNULL(leads.last_name,''))))\n\t\t\t\t\t\tWHEN 'Accounts' THEN accounts.name\n\t\t\t\t\t\tWHEN 'Users' THEN LTRIM(RTRIM(CONCAT(IFNULL(users.first_name,''),' ',IFNULL(users.last_name,''))))\n\t\t\t\t\t\tWHEN 'Prospects' THEN LTRIM(RTRIM(CONCAT(IFNULL(prospects.first_name,''),' ',IFNULL(prospects.last_name,''))))\n\t\t\t\t\tEND) recipient_name FROM emailman\n\t\t LEFT JOIN users ON users.id = emailman.related_id and emailman.related_type ='Users'\n\t\t\t\t\tLEFT JOIN contacts ON contacts.id = emailman.related_id and emailman.related_type ='Contacts'\n\t\t\t\t\tLEFT JOIN leads ON leads.id = emailman.related_id and emailman.related_type ='Leads'\n\t\t\t\t\tLEFT JOIN accounts ON accounts.id = emailman.related_id and emailman.related_type ='Accounts'\n\t\t\t\t\tLEFT JOIN prospects ON prospects.id = emailman.related_id and emailman.related_type ='Prospects'\n\t\t\t\t\tLEFT JOIN prospect_lists ON prospect_lists.id = emailman.list_id\n LEFT JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = emailman.related_id and emailman.related_type = email_addr_bean_rel.bean_module and email_addr_bean_rel.primary_address = 1 and email_addr_bean_rel.deleted=0\n\t\t\t\t\tLEFT JOIN campaigns ON campaigns.id = emailman.campaign_id\n\t\t\t\t\tLEFT JOIN email_marketing ON email_marketing.id = emailman.marketing_id INNER JOIN (select min(id) as id from emailman em GROUP BY em.user_id ) secondary\n\t\t\t on emailman.id = secondary.id\tWHERE emailman.user_id=\"\" AND emailman.deleted=0"; $actual = $emailMan->create_queue_items_query('emailman.id', 'emailman.user_id=""', array(), array('group_by' => 'emailman.user_id')); $this->assertSame($expected, $actual); }
function get_queue_items() { //get arguments being passed in $args = func_get_args(); $mkt_id = ''; $this->load_relationship('queueitems'); $query_array = $this->queueitems->getQuery(true); //if one of the arguments is marketing ID, then we need to filter by it foreach ($args as $arg) { if (isset($arg['EMAIL_MARKETING_ID_VALUE'])) { $mkt_id = $arg['EMAIL_MARKETING_ID_VALUE']; } if (isset($arg['group_by'])) { $query_array['group_by'] = $arg['group_by']; } } //add filtering by marketing id, if it exists, and if where key is not empty if (!empty($mkt_id) && !empty($query_array['where'])) { $query_array['where'] = $query_array['where'] . " AND marketing_id ='{$mkt_id}' "; } //get select query from email man $man = new EmailMan(); $listquery = $man->create_queue_items_query('', str_replace(array("WHERE", "where"), "", $query_array['where']), null, $query_array); return $listquery; }