438 lines
9.1 KiB
PHP
438 lines
9.1 KiB
PHP
<?php
|
||
|
||
class MailingDAL {
|
||
|
||
public function GetAll($offset = 0, $limit = 50) {
|
||
$db = Registry::Get('db');
|
||
|
||
$sql = " SELECT ".
|
||
" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
|
||
" FROM mf_mailing_archive ".
|
||
" ORDER BY id_mf_mailing_archive LIMIT ".$offset.", ".$limit;
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->execute($sql);
|
||
|
||
while (($row = $stmt->FetchArray())) {
|
||
$obj = new Mailing(
|
||
$row['id_mf_mailing_archive'],
|
||
$row['title'],
|
||
$row['text'],
|
||
$row['last_send'],
|
||
$row['date_send'],
|
||
$row['type'],
|
||
$row['executed']
|
||
);
|
||
|
||
$arrayObj[] = $obj;
|
||
}
|
||
if(isset($arrayObj)) {
|
||
return $arrayObj;
|
||
}
|
||
}
|
||
|
||
public function GetCountAll() {
|
||
|
||
$db = Registry::Get('db');
|
||
$sql = " SELECT count(*) as ile ".
|
||
" FROM mf_mailing_archive ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->execute();
|
||
|
||
$array = $stmt->fetchAllAssoc();
|
||
return $array[0]["ile"];
|
||
}
|
||
|
||
public function GetMailingToSend() {
|
||
$db = Registry::Get('db');
|
||
|
||
$sql = " SELECT ".
|
||
" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
|
||
" FROM mf_mailing_archive ".
|
||
" WHERE executed = 0";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->execute($sql);
|
||
|
||
if (($row = $stmt->FetchArray())) {
|
||
$obj = new Mailing(
|
||
$row['id_mf_mailing_archive'],
|
||
$row['title'],
|
||
$row['text'],
|
||
$row['last_send'],
|
||
$row['date_send'],
|
||
$row['type'],
|
||
$row['executed']
|
||
);
|
||
}
|
||
if(isset($obj)) {
|
||
return $obj;
|
||
}
|
||
}
|
||
|
||
public function GetMailingById($id) {
|
||
$db = Registry::Get('db');
|
||
|
||
$sql = " SELECT ".
|
||
" id_mf_mailing_archive, title, text, last_send, date_send, type, executed ".
|
||
" FROM mf_mailing_archive ".
|
||
" WHERE id_mf_mailing_archive = $id";
|
||
|
||
//Utils::ArrayDisplay($sql);
|
||
$stmt = $db->prepare($sql)
|
||
->execute($sql);
|
||
|
||
if (($row = $stmt->FetchArray())) {
|
||
$obj = new Mailing(
|
||
$row['id_mf_mailing_archive'],
|
||
$row['title'],
|
||
$row['text'],
|
||
$row['last_send'],
|
||
$row['date_send'],
|
||
$row['type'],
|
||
$row['executed']
|
||
);
|
||
}
|
||
if(isset($obj)) {
|
||
return $obj;
|
||
}
|
||
}
|
||
|
||
|
||
public function CheckConfirm($code) {
|
||
$db = Registry::Get('db');
|
||
|
||
$sql = " SELECT ".
|
||
" id_mf_subscription ".
|
||
" FROM mf_subscription ".
|
||
" WHERE MD5(email) = '$code'";
|
||
|
||
//Utils::ArrayDisplay($sql);
|
||
$stmt = $db->prepare($sql)
|
||
->execute($sql);
|
||
|
||
if (($row = $stmt->FetchArray())) {
|
||
$sql = "UPDATE mf_subscription SET
|
||
`confirmed` = 1,
|
||
`active` = 1
|
||
WHERE `id_mf_subscription` = :1 ";
|
||
|
||
//Utils::ArrayDisplay($sql);
|
||
$db->prepare($sql)
|
||
->bindParam(1, $row['id_mf_subscription'] )
|
||
->execute();
|
||
return true;
|
||
}
|
||
else {
|
||
return false;
|
||
}
|
||
}
|
||
|
||
public static function GetMailingAddressList($type = 1, $offset = 0, $limit = 50) {
|
||
//$limit = $offset+5;
|
||
//Utils::ArrayDisplay($offset." = ".$limit);
|
||
$db = Registry::Get('db');
|
||
if ($type == 1) {
|
||
$sql = " SELECT login, name, surname FROM mf_user LIMIT ".$offset.", ".$limit;
|
||
//Utils::ArrayDisplay(" ".$sql);
|
||
$stmt=$db->prepare($sql)
|
||
->execute();
|
||
$arrayUser = array();
|
||
$arrayPackage = array();
|
||
while (($row = $stmt->FetchArray())) {
|
||
$arrayUser[] = array(
|
||
'email' => $row['login'],
|
||
'name' => $row['name']." ".$row['surname']
|
||
);
|
||
|
||
if (count($arrayUser) == 5) {
|
||
$arrayPackage[] = $arrayUser;
|
||
$arrayUser = array();
|
||
}
|
||
}
|
||
if (count($arrayUser) > 0) {
|
||
|
||
$arrayLast[] = $arrayUser;
|
||
$arrayPackage = array_merge($arrayPackage, $arrayLast);
|
||
|
||
}
|
||
|
||
//Utils::ArrayDisplay($arrayPackage);
|
||
} elseif ($type == 2) {
|
||
$sql = " SELECT email, name FROM mf_subscription LIMIT ".$offset.", ".$limit;
|
||
//Utils::ArrayDisplay($sql);
|
||
$stmt=$db->prepare($sql)
|
||
->execute();
|
||
$arrayUser = array();
|
||
$arrayPackage = array();
|
||
while (($row = $stmt->FetchArray())) {
|
||
|
||
$arrayUser[] = array(
|
||
'email' => $row['email'],
|
||
'name' => $row['name']
|
||
);
|
||
|
||
|
||
if (count($arrayUser) == 5) {
|
||
$arrayPackage[] = $arrayUser;
|
||
$arrayUser = array();
|
||
}
|
||
|
||
}
|
||
//Utils::ArrayDisplay($arrayUser);
|
||
|
||
if (count($arrayUser) > 0) {
|
||
|
||
$arrayLast[] = $arrayUser;
|
||
$arrayPackage = array_merge($arrayPackage, $arrayLast);
|
||
|
||
}
|
||
//Utils::ArrayDisplay($arrayPackage);
|
||
|
||
}
|
||
if (isset($arrayPackage)) {
|
||
return $arrayPackage;
|
||
}
|
||
}
|
||
|
||
public function GetAllEmail($offset, $limit = 50) {
|
||
$db = Registry::Get('db');
|
||
|
||
$sql = " SELECT id_mf_subscription, email, name FROM mf_subscription ".
|
||
" ORDER BY email ".
|
||
" LIMIT ".$offset.", ".$limit
|
||
;
|
||
//Utils::ArrayDisplay($sql);
|
||
$stmt=$db->prepare($sql)
|
||
->execute();
|
||
// $arrayUser = array();
|
||
// $arrayPackage = array();
|
||
while (($row = $stmt->FetchArray())) {
|
||
$arrayUser[] = array(
|
||
'email' => $row['email'],
|
||
'name' => $row['name'],
|
||
'id' => $row['id_mf_subscription']
|
||
);
|
||
}
|
||
if (isset($arrayUser)) {
|
||
return $arrayUser;
|
||
}
|
||
}
|
||
|
||
public function GetCountAllEmail() {
|
||
|
||
$db = Registry::Get('db');
|
||
$sql = " SELECT count(*) as ile ".
|
||
" FROM mf_subscription ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->execute();
|
||
|
||
$array = $stmt->fetchAllAssoc();
|
||
return $array[0]["ile"];
|
||
}
|
||
|
||
/**
|
||
* Usuwa baner
|
||
*
|
||
* @param Int/array $id
|
||
*/
|
||
public static function Delete($id) {
|
||
if (isset($id)) {
|
||
if (is_array($id)) {
|
||
$id_where = implode(",", $id);
|
||
}
|
||
else {
|
||
$id_where = $id;
|
||
}
|
||
}
|
||
$db = Registry::Get('db');
|
||
$sql = "DELETE FROM mf_subscription WHERE id_mf_subscription IN ($id_where)";
|
||
$db->prepare($sql)
|
||
->bindParam(1, $id_where )
|
||
->execute();
|
||
}
|
||
|
||
|
||
|
||
public function InsertEmial($email, $name) {
|
||
$db = Registry::Get('db');
|
||
$sql = " INSERT INTO mf_subscription (`email`, `name`, `date_add`) ".
|
||
" VALUES (:01, :02, NOW()); ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $email)
|
||
->bindParam("02", $name)
|
||
->execute();
|
||
|
||
$insertionId = $stmt->GetInsertionId();
|
||
return $insertionId;
|
||
}
|
||
|
||
/**
|
||
* Pobiera tablice id nieaktywnych user'<27>w
|
||
*
|
||
* @return User
|
||
*/
|
||
public static function CheckInactiveUser() {
|
||
$db = Registry::Get('db');
|
||
$sql = " SELECT ".
|
||
" id_mf_subscription ".
|
||
" FROM mf_subscription ".
|
||
" WHERE date_add < (SELECT TIMESTAMPADD(DAY, -1 ,NOW())) ".
|
||
" AND active = 0 ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->execute();
|
||
while (($row = $stmt->FetchArray())) {
|
||
$arrayInactiveUser[] = $row['id_mf_subscription'];
|
||
}
|
||
if(isset($arrayInactiveUser)) {
|
||
return $arrayInactiveUser;
|
||
}
|
||
}
|
||
|
||
/**
|
||
* usuwa nieaktywnych/niepotwierdzonych user'ów
|
||
*
|
||
* @return count
|
||
*/
|
||
public static function ClearInactiveUser() {
|
||
$id = MailingDAL::CheckInactiveUser();
|
||
$logger = LoggerManager::getLogger(__CLASS__);
|
||
$logger->debug("Czyszczenie niepotwierdzonych emaili");
|
||
//Utils::ArrayDisplay($inactiveUser);
|
||
if (isset($id)) {
|
||
if (is_array($id)) {
|
||
$id_where = implode(",", $id);
|
||
}
|
||
else {
|
||
$id_where = $id;
|
||
}
|
||
}
|
||
$db = Registry::Get('db');
|
||
$sql = "DELETE FROM mf_subscription WHERE id_mf_subscription IN ($id_where)";
|
||
$stmt = $db->prepare($sql)
|
||
->execute();
|
||
|
||
}
|
||
|
||
|
||
|
||
|
||
|
||
public function UpdateLastSend($id, $offset) {
|
||
$db = Registry::Get('db');
|
||
$sql = "UPDATE mf_mailing_archive SET
|
||
`last_send` = :01
|
||
WHERE `id_mf_mailing_archive` = :02 ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $offset)
|
||
->bindParam("02", $id)
|
||
->execute();
|
||
}
|
||
|
||
public function UpdateType($id) {
|
||
$db = Registry::Get('db');
|
||
$sql = "UPDATE mf_mailing_archive SET
|
||
`type` = 2
|
||
WHERE `id_mf_mailing_archive` = :01 ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $id)
|
||
->execute();
|
||
}
|
||
|
||
public function SetExecuted($id) {
|
||
$db = Registry::Get('db');
|
||
$sql = "UPDATE mf_mailing_archive SET
|
||
`executed` = 1
|
||
WHERE `id_mf_mailing_archive` = :01 ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $id)
|
||
->execute();
|
||
}
|
||
|
||
|
||
/**
|
||
* Dodaje nowe pytanie
|
||
*
|
||
* @param query $obj
|
||
*/
|
||
public static function Insert(Mailing $obj) {
|
||
|
||
|
||
$db = Registry::Get('db');
|
||
$sql = " INSERT INTO mf_mailing_archive (`title`, `text`, `last_send`, `date_send`, `type`, `executed`) ".
|
||
" VALUES (:01, :02, :03, NOW(), :05, :06); ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $obj->GetTitle())
|
||
->bindParam("02", $obj->GetText())
|
||
->bindParam("03", $obj->GetLastSend())
|
||
->bindParam("04", $obj->GetDateSend())
|
||
->bindParam("05", $obj->GetType())
|
||
->bindParam("06", $obj->GetExecuted())
|
||
->execute();
|
||
|
||
$insertionId = $stmt->GetInsertionId();
|
||
|
||
|
||
return $insertionId;
|
||
}
|
||
|
||
|
||
/**
|
||
* Modyfikuje dane pytania
|
||
*
|
||
* @param Query $obj
|
||
*/
|
||
public static function Update(Mailing $obj) {
|
||
$db = Registry::Get('db');
|
||
$sql = "UPDATE mf_mailing_archive SET
|
||
`title` = :01,
|
||
`text` = :02,
|
||
`last_send` = :03,
|
||
`date_send` = :04,
|
||
`type` = :05,
|
||
`executed` = :06
|
||
WHERE `id_mf_mailing_archive` = :07 ";
|
||
|
||
$stmt = $db->prepare($sql)
|
||
->bindParam("01", $obj->GetTitle())
|
||
->bindParam("02", $obj->GetText())
|
||
->bindParam("03", $obj->GetLastSend())
|
||
->bindParam("04", $obj->GetDateSend())
|
||
->bindParam("05", $obj->GetType())
|
||
->bindParam("06", $obj->GetExecuted())
|
||
->bindParam("07", $obj->GetId())
|
||
->execute();
|
||
|
||
return $obj->GetId();
|
||
|
||
}
|
||
|
||
/**
|
||
* Zapisuje modyfikuje pytanie
|
||
*
|
||
* @param Query $obj
|
||
*/
|
||
public function Save(Mailing $obj) {
|
||
$id = $obj->GetId();
|
||
if ($id > 0) {
|
||
MailingDAL::Update($obj);
|
||
//$admin = AuthDAL::GetAdmin();
|
||
} else {
|
||
$id = MailingDAL::Insert($obj);
|
||
//$admin = AuthDAL::GetAdmin();
|
||
}
|
||
return $id;
|
||
}
|
||
|
||
|
||
}
|
||
|
||
?>
|