freenetis-github/application/models/email_queue.php @ 4b851957
| 8baed187 | Michal Kliment | <?php defined('SYSPATH') or die('No direct script access.');
|
|
/*
|
|||
* This file is part of open source system FreenetIS
|
|||
* and it is release under GPLv3 licence.
|
|||
*
|
|||
* More info about licence can be found:
|
|||
* http://www.gnu.org/licenses/gpl-3.0.html
|
|||
*
|
|||
* More info about project can be found:
|
|||
* http://www.freenetis.org/
|
|||
*
|
|||
*/
|
|||
/**
|
|||
* Email que represents que of unsended emails and list of sended emails.
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @package Model
|
|||
*
|
|||
* @property integer $id
|
|||
* @property string $from
|
|||
* @property string $to
|
|||
* @property string $subject
|
|||
* @property string $body
|
|||
* @property integer $state
|
|||
* @property timestamp $access_time
|
|||
*/
|
|||
class Email_queue_Model extends ORM
|
|||
{
|
|||
/**
|
|||
* New e-mail in queue
|
|||
*/
|
|||
const STATE_NEW = 0;
|
|||
/**
|
|||
* Successfully sent e-mail
|
|||
*/
|
|||
const STATE_OK = 1;
|
|||
/**
|
|||
* Unsuccessfully sent e-mail, almost same as new
|
|||
*/
|
|||
| c1bdc1c4 | Michal Kliment | const STATE_FAIL = 2;
|
|
| 8baed187 | Michal Kliment | ||
/**
|
|||
* Returns current email queue, by default 10 e-mails to send
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param integer $count
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_current_queue($count = 10)
|
|||
{
|
|||
return $this->where('state <> ',self::STATE_OK)
|
|||
->orderby('access_time')
|
|||
->limit($count,0)
|
|||
->find_all();
|
|||
}
|
|||
/**
|
|||
* Returns all sent e-mails
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @param string $filter_sql
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_sent_emails(
|
|||
$limit_from = 0, $limit_results = 50,
|
|||
$order_by = 'id', $order_by_direction = 'ASC', $filter_sql='')
|
|||
{
|
|||
| c1bdc1c4 | Michal Kliment | // args
|
|
$args = array(Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK);
|
|||
| 8baed187 | Michal Kliment | ||
| c1bdc1c4 | Michal Kliment | // sql body
|
|
$body = "SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
|||
fuc.user_id AS from_user_id,
|
|||
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|||
tuc.user_id AS to_user_id,
|
|||
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|||
| 8baed187 | Michal Kliment | FROM email_queues eq
|
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
| 2a205303 | Michal Kliment | WHERE eq.state = ?
|
|
GROUP BY eq.id";
|
|||
| c1bdc1c4 | Michal Kliment | ||
// filter
|
|||
if (empty($filter_sql))
|
|||
{
|
|||
return $this->db->query("
|
|||
$body
|
|||
ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
|
|||
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
|
|||
", $args);
|
|||
}
|
|||
else
|
|||
{
|
|||
return $this->db->query("
|
|||
$body
|
|||
HAVING $filter_sql
|
|||
ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
|
|||
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
|
|||
", $args);
|
|||
}
|
|||
| 8baed187 | Michal Kliment | }
|
|
/**
|
|||
* Counts all sent e-mails
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $filter_sql
|
|||
* @return integer
|
|||
*/
|
|||
public function count_all_sent_emails($filter_sql='')
|
|||
{
|
|||
| c1bdc1c4 | Michal Kliment | if (empty($filter_sql))
|
|
{
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS total
|
|||
FROM email_queues eq
|
|||
WHERE eq.state = ?
|
|||
", self::STATE_OK)->current()->total;
|
|||
}
|
|||
| 8baed187 | Michal Kliment | // filter
|
|
| 4b851957 | Ondřej Fibich | $where = "";
|
|
| 8baed187 | Michal Kliment | if ($filter_sql != '')
|
|
| 4b851957 | Ondřej Fibich | $where = "WHERE $filter_sql";
|
|
| 8baed187 | Michal Kliment | return $this->db->query("
|
|
| 4b851957 | Ondřej Fibich | SELECT COUNT(eq.id) as count
|
|
FROM
|
|||
(
|
|||
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
|||
fuc.user_id AS from_user_id,
|
|||
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|||
tuc.user_id AS to_user_id,
|
|||
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|||
FROM email_queues eq
|
|||
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
WHERE eq.state = ?
|
|||
) eq
|
|||
$where
|
|||
| c1bdc1c4 | Michal Kliment | ", array
|
|
(
|
|||
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
|
|||
self::STATE_OK
|
|||
| 4b851957 | Ondřej Fibich | ))->current()->count;
|
|
| c1bdc1c4 | Michal Kliment | }
|
|
/**
|
|||
* Returns all sent e-mails for export
|
|||
*
|
|||
* @param string $filter_sql
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_sent_emails_for_export($filter_sql = '')
|
|||
{
|
|||
// filter
|
|||
$having = '';
|
|||
if (!empty($filter_sql))
|
|||
$having = "HAVING $filter_sql";
|
|||
return $this->db->query("
|
|||
SELECT eq.id,
|
|||
CONCAT(eq.from, ' (', IFNULL(CONCAT(from_user_id, ' - ', from_user_name),'-'), ')') AS sender,
|
|||
CONCAT(eq.to, ' (', IFNULL(CONCAT(to_user_id, ' - ', to_user_name),'-'), ')') AS receiver,
|
|||
eq.access_time AS sended,
|
|||
eq.subject,
|
|||
eq.body AS message
|
|||
FROM
|
|||
| 8baed187 | Michal Kliment | (
|
|
| c1bdc1c4 | Michal Kliment | SELECT eq.*, fuc.user_id AS from_user_id,
|
|
| 8baed187 | Michal Kliment | CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|
tuc.user_id AS to_user_id,
|
|||
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|||
| c1bdc1c4 | Michal Kliment | FROM email_queues eq
|
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
WHERE eq.state = ?
|
|||
| 2a205303 | Michal Kliment | GROUP BY eq.id
|
|
| c1bdc1c4 | Michal Kliment | $having
|
|
| 8baed187 | Michal Kliment | ) eq
|
|
| c1bdc1c4 | Michal Kliment | ", Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK);
|
|
}
|
|||
/**
|
|||
* Returns all sent e-mails for export
|
|||
*
|
|||
* @param string $filter_sql
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function delete_sent_emails($filter_sql = '')
|
|||
{
|
|||
// filter
|
|||
$having = '';
|
|||
if (!empty($filter_sql))
|
|||
$having = "HAVING $filter_sql";
|
|||
// cannot select from deleted table, so.. two step function
|
|||
$ids = $this->db->query("
|
|||
SELECT eq.*, fuc.user_id AS from_user_id,
|
|||
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|||
tuc.user_id AS to_user_id,
|
|||
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|||
FROM email_queues eq
|
|||
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
WHERE eq.state = ?
|
|||
| 2a205303 | Michal Kliment | GROUP BY eq.id
|
|
| c1bdc1c4 | Michal Kliment | $having
|
|
", array
|
|||
(
|
|||
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
|
|||
self::STATE_OK
|
|||
))->as_array();
|
|||
$pids = array();
|
|||
foreach ($ids as $id)
|
|||
{
|
|||
$pids[] = $id->id;
|
|||
}
|
|||
$this->db->query("
|
|||
DELETE FROM email_queues WHERE id IN (" . implode(',', $pids) . ")
|
|||
");
|
|||
| 8baed187 | Michal Kliment | }
|
|
/**
|
|||
* Returns all unsent e-mails
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param integer $limit_from
|
|||
* @param integer $limit_results
|
|||
* @param string $order_by
|
|||
* @param string $order_by_direction
|
|||
* @param string $filter_sql
|
|||
* @return Mysql_Result
|
|||
*/
|
|||
public function get_all_unsent_emails(
|
|||
$limit_from = 0, $limit_results = 50,
|
|||
$order_by = 'id', $order_by_direction = 'ASC', $filter_sql='')
|
|||
{
|
|||
// filter
|
|||
| c1bdc1c4 | Michal Kliment | $having = "";
|
|
| 8baed187 | Michal Kliment | if ($filter_sql != '')
|
|
| c1bdc1c4 | Michal Kliment | $having = 'HAVING '.$filter_sql;
|
|
| 8baed187 | Michal Kliment | ||
return $this->db->query("
|
|||
| c1bdc1c4 | Michal Kliment | SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
|
fuc.user_id,
|
|||
| 8baed187 | Michal Kliment | CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|
| c1bdc1c4 | Michal Kliment | tuc.user_id,
|
|
| 8baed187 | Michal Kliment | CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|
| c1bdc1c4 | Michal Kliment | FROM email_queues eq
|
|
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
WHERE eq.state <> ?
|
|||
| 2a205303 | Michal Kliment | GROUP BY eq.id
|
|
| c1bdc1c4 | Michal Kliment | $having
|
|
| 8baed187 | Michal Kliment | ORDER BY ".$this->db->escape_column($order_by)." $order_by_direction
|
|
LIMIT " . intval($limit_from) . "," . intval($limit_results) . "
|
|||
", Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK);
|
|||
}
|
|||
/**
|
|||
* Counts all unsent e-mails
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param string $filter_sql
|
|||
* @return integer
|
|||
*/
|
|||
public function count_all_unsent_emails($filter_sql='')
|
|||
{
|
|||
| c1bdc1c4 | Michal Kliment | if (empty($filter_sql))
|
|
{
|
|||
return $this->db->query("
|
|||
SELECT COUNT(*) AS total
|
|||
FROM email_queues eq
|
|||
WHERE eq.state <> ?
|
|||
", self::STATE_OK)->current()->total;
|
|||
}
|
|||
| 8baed187 | Michal Kliment | // filter
|
|
| 4b851957 | Ondřej Fibich | $where = "";
|
|
| 8baed187 | Michal Kliment | if ($filter_sql != '')
|
|
| 4b851957 | Ondřej Fibich | $where = 'WHERE '.$filter_sql;
|
|
| 8baed187 | Michal Kliment | return $this->db->query("
|
|
| 4b851957 | Ondřej Fibich | SELECT COUNT(eq.id) as count
|
|
FROM
|
|||
(
|
|||
SELECT eq.id, eq.from, eq.to, eq.subject, eq.state, eq.access_time,
|
|||
fuc.user_id AS from_user_id,
|
|||
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
|
|||
tuc.user_id AS to_user_id,
|
|||
CONCAT(tu.name,' ',tu.surname) AS to_user_name
|
|||
FROM email_queues eq
|
|||
LEFT JOIN contacts fc ON eq.from = fc.value AND fc.type = ?
|
|||
LEFT JOIN users_contacts fuc ON fc.id = fuc.contact_id
|
|||
LEFT JOIN users fu ON fuc.user_id = fu.id
|
|||
LEFT JOIN contacts tc ON eq.to = tc.value AND tc.type = ?
|
|||
LEFT JOIN users_contacts tuc ON tc.id = tuc.contact_id
|
|||
LEFT JOIN users tu ON tuc.user_id = tu.id
|
|||
) eq
|
|||
$where
|
|||
| c1bdc1c4 | Michal Kliment | ", array
|
|
(
|
|||
| 4b851957 | Ondřej Fibich | self::STATE_OK,
|
|
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL
|
|||
))->current()->count;
|
|||
| 8baed187 | Michal Kliment | }
|
|
/**
|
|||
* Adds message to the beginning of queue (will be send first)
|
|||
*
|
|||
* @author Michal Kliment
|
|||
* @param type $from
|
|||
* @param type $to
|
|||
* @param type $subject
|
|||
* @param type $body
|
|||
* @return type
|
|||
*/
|
|||
public function push($from, $to, $subject, $body)
|
|||
{
|
|||
return $this->db->query("
|
|||
INSERT INTO email_queues
|
|||
SELECT
|
|||
NULL, ?, ?, ?, ?, ?,
|
|||
FROM_UNIXTIME(UNIX_TIMESTAMP(MIN(access_time))-1)
|
|||
FROM email_queues
|
|||
", array($from, $to, $subject, $body, self::STATE_NEW));
|
|||
}
|
|||
}
|