Viewing file:  model.inc.php (18.64 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
class List_Model
{
public static function AddPetition($row)
{
if (strlen(trim($row['theme'])) < 5 || strlen(trim($row['text'])) < 20)
return 0;
$row = Main_Model::ArrayFilter($row, array('theme', 'text'));
$row['petition_user_id'] = Users_Model::GetUserId();
Users_Model::IncPetitionsCount($row['petition_user_id']);
$row['date'] = Main_Model::GetNowDate();
$row['number'] = Main_Model::GetNowYear();
$res = Main_Model::ExecuteQuery("INSERT INTO `petitions` (`petition_id`, `petition_user_id`, `petition_theme`, `petition_text`, `petition_date`, `petition_number`) VALUES (NULL, :petition_user_id, :theme, :text, :date, :number)", $row);
$id = Main_Model::$DB->lastInsertId();
self::SignPetition($id, false);
return $id;
}
public static function GetPetitionsCount($status)
{
if (($status <= 0 || $status > 4) && !Users_Model::IsAdmin())
$status = 1;
if ($status == -1)
{
$sql = "SELECT COUNT(*) as count FROM petitions WHERE petition_status < 0";
} else
{
$sql = "SELECT COUNT(*) as count FROM petitions WHERE petition_status = '{$status}'";
}
$sth = Main_Model::ExecuteQuery($sql);
$row = $sth->fetch();
return $row['count'];
}
public static function AddNumber()
{
$year = Main_Model::GetNowYear();
$res = Main_Model::ExecuteQuery("SELECT * FROM numbers WHERE year = '{$year}'");
$row = $res->fetchAll();
$row = $row[0];
if (empty($row))
$res = Main_Model::ExecuteQuery("INSERT INTO numbers (year,value) VALUES ('{$year}', 0)");
else {
$number = $row['number'] + 1;
$res = Main_Model::ExecuteQuery("UPDATE numbers SET number = '{$number}' WHERE year = '{$year}'");
}
return $number;
}
public static function ConfirmPetition($id)
{
$petition = self::GetPetitionByIdForAdmin($id);
if (strlen($petition['petition_number']) < 5)
$row['num'] = Main_Model::GetNowYear().'-'.self::AddNumber();
else
$row['num'] = $petition['petition_number'];
$row['date'] = Main_Model::GetNowDate();
$row['id'] = $id;
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = '1', petition_publication_date = :date, petition_number = :num WHERE petition_id = :id", $row);
}
public static function SendDeleteInfoMessage($petition_id)
{
$petition = self::GetPetitionByIdForAdmin($petition_id);
$user = Users_Model::GetUserById($petition['petition_user_id']);
$html = List_View::DeleteInfoMessage($petition, $user);
$headers = "MIME-Version: 1.0\r\n";
$headers .= "Content-type: text/html; charset=utf-8\r\n";
$headers .= 'From: petitions@zt-rada.gov.ua' . "\r\n";
mail($user['user_email'], 'Ваша петиція видалена з сайту петицій до Житомирської міської ради', $html, $headers);
}
public static function SetDeleteStatusOfPetition($id, $text = null, $sendMail = false)
{
$petition = self::GetPetitionByIdForAdmin($id);
$row = array();
$row['id'] = $id;
if ($petition['petition_status'] < 0)
$res = Main_Model::ExecuteQuery("DELETE FROM `petitions` WHERE petition_id = :id", $row);
else {
$row['text'] = empty($text) ? $petition['petition_reject_message'] : $text;
if ($petition['petition_status'] == 0)
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = -10, petition_reject_message = :text WHERE petition_id = :id", $row);
else
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = -abs(petition_status), petition_reject_message = :text WHERE petition_id = :id", $row);
}
if ($sendMail)
List_Model::SendDeleteInfoMessage($id);
}
public static function RestorePetition($id)
{
$petition = self::GetPetitionByIdForAdmin($id);
$row = array();
$row['id'] = $id;
if ($petition['petition_status'] == -10)
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = 0, petition_reject_message = '' WHERE petition_id = :id", $row);
else
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = abs(petition_status), petition_reject_message = '' WHERE petition_id = :id", $row);
}
public static function SetPetitionAnswer($text, $id, $status = 2)
{
$row = array('id' => $id, 'text' => $text, 'status' => $status);
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = :status, petition_answer_text = :text WHERE petition_id = :id", $row);
}
public static function EditPetition($theme, $text, $number, $id)
{
$row = array('id' => $id, 'theme' => $theme, 'text' => $text, 'number' => $number);
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_theme = :theme, petition_number = :number, petition_text = :text WHERE petition_id = :id", $row);
}
public static function PetitionCollected($id)
{
$row = array('id' => $id, 'date' => Main_Model::GetNowDate());
$res = Main_Model::ExecuteQuery("UPDATE `petitions` SET petition_status = '2', petition_collected_date = :date WHERE petition_id = :id", $row);
}
public static function SetNotSupportedFlags()
{
$days = Params_Model::DaysCount();
$daysNew = Params_Model::DaysCountNew();
$sql = "UPDATE petitions SET petition_status = 4 WHERE (((($days*24*60*60 - TIMESTAMPDIFF(second,petition_publication_date,NOW())) < 0) AND (petition_id < 1468)) OR ((($daysNew*24*60*60 - TIMESTAMPDIFF(second,petition_publication_date,NOW())) < 0) AND (petition_id >= 1468))) AND petition_status = 1";
$res = Main_Model::ExecuteQuery($sql);
}
public static function GetCountOfTypes()
{
$sql = "SELECT COUNT(*) as c , petition_status FROM petitions GROUP BY petition_status";
$res = Main_Model::ExecuteQuery($sql);
$rows = $res->fetchAll();
$result = array();
for($i = 0; $i < count($rows); $i++)
{
if ($rows[$i]['petition_status'] < 0)
$result[100] += $rows[$i]['c'];
else
$result[$rows[$i]['petition_status']] += $rows[$i]['c'];
}
return $result;
}
public static function GetPetitionsList($sorting = 0, $start = 0, $count = 1000000, $status = 1)
{
switch($sorting)
{
case 0: $orderby = "petition_publication_date DESC"; break;
case 1: $orderby = "petition_publication_date ASC"; break;
case 2: $orderby = "petition_signs_count DESC"; break;
case 3: $orderby = "petition_signs_count ASC"; break;
default: $orderby = "petition_publication_date DESC";
}
if ($status >= 0)
$res = Main_Model::ExecuteQuery("SELECT petition_id, petition_date, petition_number, petition_publication_date, petition_signs_count, petition_theme, petition_status FROM petitions WHERE petition_status = '{$status}' ORDER BY {$orderby} LIMIT {$start}, {$count}");
if ($status < 0 && Users_Model::IsAdmin())
$res = Main_Model::ExecuteQuery("SELECT * FROM petitions WHERE petition_status < 0 ORDER BY {$orderby} LIMIT {$start}, {$count}");
$rows = $res->fetchAll();
for($i = 0; $i < count($rows); $i++) {
$rows[$i]['petition_rest_seconds'] = 0;
if ($rows[$i]['petition_status'] == 1) {
$rows[$i]['petition_rest'] = List_Model::GetRestOfDays($rows[$i]['petition_id'], $rows[$i]['petition_publication_date']);
$rows[$i]['petition_rest_seconds'] = List_Model::GetRestOfTime($rows[$i]['petition_id'], $rows[$i]['petition_publication_date']);
}
else
$rows[$i]['petition_rest'] = "-";
$rows[$i]['petition_percent'] = round($rows[$i]['petition_signs_count'] / Params_Model::VotesCount($rows[$i]['petition_id']) * 100);
$rows[$i]['petition_usefuldate'] = Main_Model::GetUsefulDate($rows[$i]['petition_date'], true, true);
if ($rows[$i]['petition_percent'] > 100)
$rows[$i]['petition_percent'] = 100;
$arr = Users_Model::GetSignsOfCurrentUser();
if (strlen($rows[$i]['petition_number']) < 5)
$rows[$i]['petition_number'] = "-";
if (in_array($rows[$i]['petition_id'], $arr))
$rows[$i]['petition_curuser_sign'] = true;
else
$rows[$i]['petition_curuser_sign'] = false;
$rows[$i]['maxcount'] = Params_Model::VotesCount($rows[$i]['petition_id']);
}
return $rows;
}
public static function GetPetitionsForAdmin($count = null, $start = 0)
{
$row=array('start' => $start, 'count' => $count == null ? 20 : $count);
//$res = Main_Model::ExecuteQuery("SELECT petitions.*, COUNT(signatures.signature_id) as count FROM petitions LEFT JOIN signatures ON petitions.petition_id = signatures.signature_petition_id GROUP BY petition_id ORDER BY petition_status ASC, petition_publication_date DESC, petition_number DESC, petition_id DESC LIMIT {$row['start']}, {$row['count']}");
$res = Main_Model::ExecuteQuery("SELECT * FROM petitions ORDER BY petition_status ASC, petition_publication_date DESC, petition_number DESC, petition_id DESC LIMIT {$row['start']}, {$row['count']}");
$rows = $res->fetchAll();
for($i = 0; $i < count($rows); $i++)
{
$res2 = Main_Model::ExecuteQuery("SELECT COUNT(*) as count FROM signatures WHERE signature_petition_id = {$row[$i]['petition_id']}");
$rows2 = $res->fetchAll();
//$rows[$i]['count'] = $rows2['count'];
}
//die;
return $rows;
}
public static function GetPetitions($count = null, $start = 0)
{
$row=array('start' => $start, 'count' => $count == null ? 20 : $count);
//$res = Main_Model::ExecuteQuery("SELECT petitions.*, COUNT(signatures.signature_id) as count FROM petitions LEFT JOIN signatures ON petitions.petition_id = signatures.signature_petition_id WHERE petition_status > 0 GROUP BY petition_id ORDER BY petition_publication_date DESC, petition_number DESC, petition_id DESC LIMIT {$row['start']}, {$row['count']}", $row);
$res = Main_Model::ExecuteQuery("SELECT petitions.* FROM petitions WHERE petition_status > 0 GROUP BY petition_id ORDER BY petition_publication_date DESC, petition_number DESC, petition_id DESC LIMIT {$row['start']}, {$row['count']}", $row);
return $res->fetchAll();
}
public static function GetPetitionById($id)
{
$res = Main_Model::ExecuteQuery("SELECT petitions.*, users.*, COUNT(signatures.signature_id) as count FROM petitions INNER JOIN users ON petitions.petition_user_id = users.user_id LEFT JOIN signatures ON petitions.petition_id = signatures.signature_petition_id WHERE petition_id = :id AND petition_status > 0", array('id' => $id));
$row = $res->fetch();
return $row['petition_status'] > 0 ? $row:null ;
}
public static function GetPetitionByIdForAdmin($id)
{
$res = Main_Model::ExecuteQuery("SELECT petitions.*, users.*, COUNT(signatures.signature_id) as count FROM petitions INNER JOIN users ON petitions.petition_user_id = users.user_id LEFT JOIN signatures ON petitions.petition_id = signatures.signature_petition_id WHERE petition_id = :id", array('id' => $id));
return $res->fetch();
}
public static function IsUserSignedPetitionTodayByUID($petition_id, $user_uid)
{
$sql = "SELECT signature_petition_id, signature_uid, COUNT(DISTINCT signature_user_id) FROM signatures WHERE signature_petition_id = :pid AND signature_uid = :uid AND DATEDIFF(DATE(signature_date), DATE(NOW())) = 0 GROUP BY signature_uid, DATE(signature_date)";
$res = Main_Model::ExecuteQuery($sql, array('uid' => $user_uid, 'pid' => $petition_id));
$rows = $res->fetchAll();
if (count($rows) > 0)
return true;
else
return false;
}
public static function IsUserSignedPetitionTodayByIP($petition_id, $user_ip)
{
$sql = "SELECT signature_petition_id, signature_uid, COUNT(DISTINCT signature_user_id) FROM signatures WHERE signature_petition_id = :pid AND signature_ip = :ip AND DATEDIFF(DATE(signature_date), DATE(NOW())) = 0 GROUP BY signature_ip, DATE(signature_date)";
$res = Main_Model::ExecuteQuery($sql, array('ip' => $user_ip, 'pid' => $petition_id));
$rows = $res->fetchAll();
if (count($rows) > 0)
return true;
else
return false;
}
public static function SignPetition($id, $testBots = true)
{
if (self::IsSigned($id)) {
return array('status' => false, 'message' => 'Петицію вже підписано');
}
$row['petition_id'] = $id;
$row['user_id'] = Users_Model::GetUserId();
$row['date'] = Main_Model::GetNowDate();
$row['ip'] = Users_Model::GetIP();
$row['uid'] = Users_Model::GetUID();
if ($testBots && empty($row['uid']))
{
return array('status' => false, 'message' => 'Помилка підписання. Оновіть сторінку. Якщо помилка не зникне, зверніться до служби підтримки.');
}
if (self::IsUserSignedPetitionTodayByUID($id, $row['uid']) && $testBots)
{
return array('status' => false, 'message' => 'З одного комп\'ютера можна підписувати петицію лише один раз');
}
// die;
if (self::IsUserSignedPetitionTodayByIP($id, $row['ip']) && $testBots)
{
return array('status' => false, 'message' => 'З однієї ІР-адреси можна голосувати лише раз на добу за одну петицію. Ви зможете проголосувати за дану петицію завтра.');
}
$petition = self::GetPetitionByIdForAdmin($id);
if ($petition['petition_status'] == 0 && $petition['petition_user_id'] != Users_Model::GetUserId())
return array('status' => false, 'message' => 'Помилка');
if ($petition['petition_status'] >= 3 || $petition['petition_status'] < 0)
return array('status' => false, 'message' => 'Не можна підписувати петицію, яка вже розглянута');
if ($petition['count'] == Params_Model::VotesCount($id) - 1)
{
self::PetitionCollected($id);
}
Users_Model::IncVotesCount(Users_Model::GetUserId());
self::IncPetitionSigns($id);
Main_Model::ExecuteQuery("INSERT INTO `signatures` (`signature_id`, `signature_user_id`, `signature_petition_id`, `signature_date`, `signature_ip`, `signature_uid`) VALUES (NULL, :user_id, :petition_id, :date, :ip, :uid)", $row);
return array('status' => true, 'message' => 'Петицію підписано');
}
/* додано перший параметр */
public static function GetRestOfDays($petition_id, $date)
{
$dformat = '-';
$endDate = strtotime(Main_Model::GetNowDate());
$beginDate = strtotime($date);
if ($petition_id < 1468)
$res = Params_Model::DaysCount() * 24*60*60 - abs($endDate - $beginDate);
else
$res = Params_Model::DaysCountNew() * 24*60*60 - abs($endDate - $beginDate);
if ($res < 0)
{
self::SetNotSupportedFlags();
}
return (int)($res / 3600 / 24);
}
/* додано перший параметр */
public static function GetRestOfSeconds($petition_id, $date)
{
$dformat = '-';
$endDate = strtotime(Main_Model::GetNowDate());
$beginDate = strtotime($date);
if ($petition_id < 1468)
$res = Params_Model::DaysCount() * 24*60*60 - abs($endDate - $beginDate);
else
$res = Params_Model::DaysCountNew() * 24*60*60 - abs($endDate - $beginDate);
if ($res < 0)
{
self::SetNotSupportedFlags();
}
return $res;
}
/* додано перший параметр */
public static function GetRestOfTime($petition_id, $date)
{
$seconds = self::GetRestOfSeconds($petition_id, $date);
$minutes = (int)($seconds / 60);
$hours = (int)($minutes / 60);
$minutes = $minutes % 60;
$str = '';
if ($hours <= 24) {
if ($hours > 0) {
$str = ($hours) . ' год. ' . ($minutes) . ' хв.';
return $str;
}
if ($minutes > 0){
$str = (minutes) . ' хв.';
return $str;
}
return '0';
}
$days = self::GetRestOfDays($petition_id, $date);
$last = $days % 10;
$prelast = $days % 100;
$p = '';
if ($prelast >= 5 && $prelast <= 20)
$p = ' днів';
else
switch($last)
{
case 1: $p = ' день';
break;
case 2: case 3: case 4: $p = ' дні';
break;
case 5: case 6: case 7: case 8: case 9: case 0: $p = ' днів';
break;
}
return $days. $p;
}
public static function IsSigned($id)
{
$row['petition_id'] = $id;
$row['user_id'] = Users_Model::GetUserId();
$res = Main_Model::ExecuteQuery("SELECT COUNT(*) FROM signatures WHERE signature_petition_id = :petition_id AND signature_user_id = :user_id", $row);
return $res->fetchColumn() > 0;
}
public static function GetSignatories($id)
{
$row['petition_id'] = $id;
$res = Main_Model::ExecuteQuery("SELECT signatures.*, users.user_firstname, users.user_lastname, users.user_middlename FROM signatures INNER JOIN users ON users.user_id = signatures.signature_user_id WHERE signature_petition_id = :petition_id", $row);
return $res->fetchAll();
}
public static function IncPetitionSigns($petition_id)
{
$sql = "UPDATE petitions SET petition_signs_count = petition_signs_count + 1 WHERE petition_id = :id";
$res = Main_Model::ExecuteQuery($sql, array('id' => $petition_id));
}
public static function UpdatePetitionCounters()
{
$sql = "UPDATE petitions SET petition_signs_count = (SELECT COUNT(*) FROM signatures WHERE signature_petition_id = petition_id)";
$res = Main_Model::ExecuteQuery($sql);
}
}
//List_Model::UpdatePetitionCounters();
|