/**
*
* @param int $id
* @return Zend_Db_Table_Rowset
*/
public function listContactsToSend($id, $limit = false)
{
$mapperGroupSms = new Sms_Model_Mapper_Group();
$selectContacts = $mapperGroupSms->getSelectContacts();
// Get the SMS Config
$mapperConfig = new Admin_Model_Mapper_SmsConfig();
$config = $mapperConfig->getConfig();
$dbCampainGroup = App_Model_DbTable_Factory::get('CampaignHasSmsGroup');
$dbCampainSent = App_Model_DbTable_Factory::get('CampaignSent');
$selectContacts->join(array('cg' => $dbCampainGroup), 'cg.fk_id_sms_group = t.fk_id_sms_group', array())->joinLeft(array('cs' => $dbCampainSent), 'cs.fk_id_campaign = cg.fk_id_campaign ' . 'AND cs.fk_id_sms_group_contact = t.id_sms_group_contact', array('id_campaign_sent', 'attempts'))->where('cg.fk_id_campaign = ?', $id)->where('( cs.id_campaign_sent IS NULL')->orWhere('cs.status = ?', 'E')->where('cs.attempts < ? )', $config->error_attempts);
if (!empty($limit)) {
$selectContacts->limit($limit);
}
return $this->_dbTable->fetchAll($selectContacts);
}
/**
*
* @return array
*/
public function incomingReport()
{
$filters = $this->_data;
$dbSmsIncoming = App_Model_DbTable_Factory::get('SmsIncoming');
$dbCampaign = App_Model_DbTable_Factory::get('Campaign');
$mapperGroupSms = new Sms_Model_Mapper_Group();
$selectContact = $mapperGroupSms->getSelectContacts();
$selectContact->reset(Zend_Db_Select::COLUMNS)->columns(array('contact', 'type', 'number'))->join(array('si' => $dbSmsIncoming), 'si.fk_id_sms_group_contact = t.id_sms_group_contact', array('date_time', 'content', 'target', 'source'))->join(array('c' => $dbCampaign), 'c.id_campaign = si.fk_id_campaign', array('campaign_title'));
if (!empty($filters['fk_id_department'])) {
$selectContact->where('c.fk_id_department = ?', $filters['fk_id_department']);
}
if (!empty($filters['fk_id_campaign'])) {
$selectContact->where('si.fk_id_campaign = ?', $filters['fk_id_campaign']);
}
$selectNonData = $dbSmsIncoming->select()->from(array('si' => $dbSmsIncoming), array('contact' => new Zend_Db_Expr('"La identifika"'), 'type' => new Zend_Db_Expr('"La identifika"'), 'number' => 'source', 'date_time', 'content', 'target', 'source', 'campaign_title' => new Zend_Db_Expr('"La identifika"')))->where('si.fk_id_sms_group_contact IS NULL')->where('si.fk_id_campaign IS NULL');
$selectNonContact = $dbSmsIncoming->select()->setIntegrityCheck(false)->from(array('si' => $dbSmsIncoming), array('contact' => new Zend_Db_Expr('"La identifika"'), 'type' => new Zend_Db_Expr('"La identifika"'), 'number' => 'source', 'date_time', 'content', 'target', 'source'))->join(array('c' => $dbCampaign), 'c.id_campaign = si.fk_id_campaign', array('campaign_title'))->where('si.fk_id_sms_group_contact IS NULL')->where('si.fk_id_campaign IS NOT NULL');
$selectNonCampaign = $dbSmsIncoming->select()->setIntegrityCheck(false)->from(array('si' => $dbSmsIncoming), array('contact' => new Zend_Db_Expr('source'), 'type' => new Zend_Db_Expr('"La identifika"'), 'number' => 'source', 'date_time', 'content', 'target', 'source', 'campaign_title' => new Zend_Db_Expr('"La identifika"')))->where('si.fk_id_sms_group_contact IS NOT NULL')->where('si.fk_id_campaign IS NULL');
$union = array($selectContact, $selectNonContact, $selectNonCampaign);
if (empty($filters['fk_id_campaign'])) {
$union[] = $selectNonData;
}
$selectUnion = $dbSmsIncoming->select()->union($union)->setIntegrityCheck(false)->order(array('date_time DESC'));
$selectFinal = $dbSmsIncoming->select()->from(array('t' => new Zend_Db_Expr('(' . $selectUnion . ')')))->setIntegrityCheck(false);
$date = new Zend_Date();
if (!empty($filters['date_start'])) {
$selectFinal->where('DATE(t.date_time) >= ?', $date->set($filters['date_start'])->toString('yyyy-MM-dd'));
}
if (!empty($filters['date_finish'])) {
$selectFinal->where('DATE(t.date_time) <= ?', $date->set($filters['date_finish'])->toString('yyyy-MM-dd'));
}
$rows = $dbSmsIncoming->fetchAll($selectFinal);
return array('rows' => $rows);
}
