Projekt

Obecné

Profil

Stáhnout (9.82 KB) Statistiky
| Větev: | Tag: | Revize:
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
74a7dbca Michal Kliment
* @property string $hash
8baed187 Michal Kliment
*/
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
*/
74a7dbca Michal Kliment
const STATE_FAIL = 2;
/**
* E-mail has been read by recipient
*/
const STATE_READ = 3;
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)
{
74a7dbca Michal Kliment
return $this
->in('state',
array(
self::STATE_NEW,
self::STATE_FAIL
)
)
8baed187 Michal Kliment
->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
74a7dbca Michal Kliment
$args = array(Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK, self::STATE_READ);
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
74a7dbca Michal Kliment
WHERE eq.state = ? OR eq.state = ? ";
c1bdc1c4 Michal Kliment
// filter
if (empty($filter_sql))
{
return $this->db->query("
$body
74a7dbca Michal Kliment
GROUP BY eq.id
c1bdc1c4 Michal Kliment
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
74a7dbca Michal Kliment
WHERE eq.state = ? OR eq.state = ?
", self::STATE_OK, self::STATE_READ)->current()->total;
c1bdc1c4 Michal Kliment
}
8baed187 Michal Kliment
// 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 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
74a7dbca Michal Kliment
WHERE eq.state = ? OR eq.state = ?
c1bdc1c4 Michal Kliment
$having
", array
(
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
74a7dbca Michal Kliment
self::STATE_OK, self::STATE_READ
c1bdc1c4 Michal Kliment
))->count();
}
/**
* 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
74a7dbca Michal Kliment
WHERE eq.state = ? OR eq.state = ?
c1bdc1c4 Michal Kliment
$having
8baed187 Michal Kliment
) eq
74a7dbca Michal Kliment
", Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK, self::STATE_READ);
c1bdc1c4 Michal Kliment
}
/**
* 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
74a7dbca Michal Kliment
WHERE eq.state = ? OR eq.state = ?
c1bdc1c4 Michal Kliment
$having
", array
(
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
74a7dbca Michal Kliment
self::STATE_OK, self::STATE_READ
c1bdc1c4 Michal Kliment
))->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
74a7dbca Michal Kliment
WHERE eq.state <> ? AND eq.state <> ?
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) . "
74a7dbca Michal Kliment
", Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL, self::STATE_OK, self::STATE_READ);
8baed187 Michal Kliment
}
/**
* 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
74a7dbca Michal Kliment
WHERE eq.state <> ? AND eq.state <> ?
", self::STATE_OK, self::STATE_READ)->current()->total;
c1bdc1c4 Michal Kliment
}
8baed187 Michal Kliment
// 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,
CONCAT(fu.name,' ',fu.surname) AS from_user_name,
tuc.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
74a7dbca Michal Kliment
WHERE eq.state <> ? AND eq.state <> ?
c1bdc1c4 Michal Kliment
$having
", array
(
Contact_Model::TYPE_EMAIL, Contact_Model::TYPE_EMAIL,
74a7dbca Michal Kliment
self::STATE_OK, self::STATE_READ
c1bdc1c4 Michal Kliment
))->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));
}
}