function createUnsubscribeKeys($letter_id, $delivery_num, $topics_ids) { global $application; $str_topics_ids = implode(',', $topics_ids); // copy emails to unsubscribe table $ntables = $this->getTables(); $stables = modApiFunc('Subscriptions', 'getTables'); $utable = 'newsletter_unsubscribe'; $ucolumns = $ntables[$utable]['columns']; $ltable = 'newsletter_topics'; $lcolumns = $ntables[$ltable]['columns']; $etable = 'subscription_email'; $ecolumns = $stables[$etable]['columns']; $atable = 'email_address'; $acolumns = $stables[$atable]['columns']; $iquery = new DB_Insert_Select($utable); $iquery->setModifiers(DB_IGNORE); $iquery->setInsertFields(array('key_unsubscribe', 'delivery_num', 'letter_id', 'email_id', 'topics_ids')); $squery = new DB_Select($etable); $squery->addSelectField('MD5(CONCAT("' . $delivery_num . '", "' . $letter_id . '", "' . $str_topics_ids . '", NOW(), ' . $ecolumns['email_id'] . ', ' . $acolumns['email'] . '))'); $squery->addSelectField($delivery_num . '-0', 'delivery_num'); $squery->addSelectField($letter_id . '+0', 'letter_id'); $squery->addSelectField($ecolumns['email_id']); $squery->addSelectField(DBQuery::quoteValue($str_topics_ids)); $squery->addInnerJoin($atable, $ecolumns['email_id'], DB_EQ, $acolumns['email_id']); $squery->Where($ecolumns['topic_id'], DB_IN, DBQuery::arrayToIn($topics_ids)); $squery->SelectGroup($ecolumns['email_id']); $squery->SelectOrder($ecolumns['email_id'], 'ASC'); $iquery->setSelectQuery($squery); $application->db->getDB_Result($iquery); $this->_totalRecipients = $this->countTempEmails($delivery_num); $this->_sentCountTotal = 0; $result = array('Errors' => array($application->db->_getSQL($iquery)), 'Warnings' => array(), 'TotalCount' => $this->_totalRecipients, 'Num' => $delivery_num); return $result; }
function subscribeTempEmails($key, $topics_ids) { global $application; $tables = Subscriptions::getTables(); $stable = 'subscription_temp'; $scolumns =& $tables[$stable]['columns']; $itable = 'subscription_email'; $icolumns =& $tables[$itable]['columns']; foreach ($topics_ids as $topic_id) { unset($squery); $squery = new DB_Select($stable); $squery->addSelectField($topic_id); $squery->addSelectField($scolumns['email_id']); $squery->Where($scolumns['action_key'], DB_EQ, DBQuery::quoteValue($key)); unset($iquery); $iquery = new DB_Insert_Select($itable); $iquery->setModifiers(DB_IGNORE); $iquery->setInsertFields(array('topic_id', 'email_id')); $iquery->setSelectQuery($squery); $application->db->getDB_Result($iquery); } }
function initQuery($params) { $key = $params['key']; $otables = Customer_Account::getTables(); $atable = 'ca_person_info_attrs'; $gtable = 'ca_attrs_to_groups'; $dtable = 'ca_person_info_data'; $stables = Subscriptions::getTables(); $stable = 'subscription_temp'; $this->setModifiers(DB_IGNORE); $this->setInsertFields(array($stables[$stable]['columns']['action_key'])); $this->setInsertFields(array($stables[$stable]['columns']['email'])); $squery = new DB_Select($dtable); $squery->addSelectField(DBQuery::quoteValue($key)); $squery->addSelectField(DBQuery::fLower($otables[$dtable]['columns']['data_value'])); $squery->addInnerJoin($gtable, $otables[$dtable]['columns']['ag_id'], DB_EQ, $otables[$gtable]['columns']['ag_id']); $squery->addInnerJoin($atable, $otables[$gtable]['columns']['attr_id'], DB_EQ, $otables[$atable]['columns']['attr_id']); $squery->WhereValue($otables[$atable]['columns']['attr_name'], DB_EQ, 'Email'); $this->setSelectQuery($squery); unset($squery); }