Skip to content

Instantly share code, notes, and snippets.

@fake-fur

fake-fur/db.class.php

Created June 5, 2014 16:01
Show Gist options
  • Save fake-fur/2ffba0aa0c55a160b95d to your computer and use it in GitHub Desktop.
Save fake-fur/2ffba0aa0c55a160b95d to your computer and use it in GitHub Desktop.
Download ZIP
tinyissue email notifications - hacky solution that works
Raw
db.class.php
<?php
class db extends mysqli {
public function __construct()
{
parent::init();
/*
set any connection options here as reqd
if (!parent::options(MYSQLI_INIT_COMMAND,'SET AUTOCOMMIT = 0')) {
die('Setting MYSQLI_INIT_COMMAND failed');
}
if (!parent::options(MYSQLI_OPT_CONNECT_TIMEOUT, 5)) {
die('Setting MYSQLI_OPT_CONNECT_TIMEOUT failed');
}
*/
if (!@parent::real_connect(DBHOST,DBUSER,DBPASS,DBNAME)){
die("Our database is temporarily offline.<br>The site administrator has been notified.<br>Please try again shortly...<hr>Status: 1337");
}
}
////////////////////////////////////////////////////////
// run a query and return the INT value or zero
////////////////////////////////////////////////////////
public function get_int($sql)
{
try {
$res = parent::query($sql);
$row = $res->fetch_row();
return $row[0];
} catch (Exception $e){
return 0;
}
}
////////////////////////////////////////////////////////
// return a single string value
////////////////////////////////////////////////////////
public function get_string($sql)
{
try {
$res = parent::query($sql);
$row = $res->fetch_row();
return $row[0];
} catch (Exception $e){
return "";
}
}
////////////////////////////////////////////////////////
// return an array of 1 db row
////////////////////////////////////////////////////////
public function get_row($sql)
{
try {
$res = parent::query($sql);
$row = $res->fetch_assoc();
return $row;
} catch (Exception $e){
return false;
}
}
////////////////////////////////////////////////////////
// return a result set as 1D array
////////////////////////////////////////////////////////
public function get_1d_array($sql)
{
try {
$a = "";
$res = parent::query($sql);
while ($row = $res->fetch_row()){
$a[] = $row[0];
}
return $a;
} catch (Exception $e){
return false;
}
}
}
?>
Raw
tiny_notify.php
<?php
// notes:
//
// this is the sql to add the extra column to the users_activity table:
// ALTER TABLE `users_activity` ADD `notify_sent` CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'N';
//
// this is the crontab line i use (change the url obviously) which runs every 5 mins:
// */5 * * * * lynx -dump http://[your-tinyissue-domain]/tiny_notify.php
//
// fakefur@gmail.com is my email in case you have any questions
// and yes i know it's a hideous hack but it works for us for now
// :)
//
set_time_limit(0);
ignore_user_abort(true);
$email_body = "<html><head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" /></head>
<body><p>Dear [[greeting]]</p><p>[[creator]] [[action]] in the project \"[[project]]\"</p>
<p>Issue Title: [[issue_title]]</p>
<p>Issue: [[issue_body]]</p>
<p>Comment: [[comment]]</p>
<p><a href=\"[[link]]\">Click here to go to the issue &raquo;</a></p>
<p>The Happy Bug Bot</p></body>
</html>";
define("DBHOST",'tinyissue-database-host');
define("DBNAME",'tinyissue-database-name');
define("DBUSER",'tinyissue-database-user');
define("DBPASS",'tinyissue-database-user-password');
require_once("db.class.php");
$db = new db();
$dbp = new db();
$dbav = new db();
// get list of activities to be processed
$resav = $dbav->query("select users.id user_id,users_activity.id,activity.id activity,projects.id project,projects_issues.id issue_id,
concat_ws(' ',users.firstname,users.lastname) creator,projects.name,
projects_issues.title,projects_issues.body,
activity.description,projects_issues_comments.comment
from users_activity
inner join users on users.id = users_activity.user_id
inner join projects on projects.id = users_activity.parent_id
inner join projects_issues on projects_issues.id = users_activity.item_id
inner join activity on activity.id = users_activity.type_id
left join projects_issues_comments on projects_issues_comments.id = users_activity.action_id
where users_activity.notification_sent = 'N'
order by users_activity.id");
while ($rowav = $resav->fetch_assoc()){
// now get all users that are on this project but not creator
$resp = $dbp->query("select concat_ws(' ',users.firstname,users.lastname) greeting,users.email
from users
inner join projects_users on projects_users.user_id=users.id
where projects_users.project_id={$rowav["project"]} and projects_users.user_id != {$rowav["user_id"]}");
// now the actual sending loop
$headers = "MIME-Version: 1.0\r\nContent-type: text/html; charset=utf-8\r\n";
while ($rowp = $resp->fetch_assoc()){
// make link
$link = "http://{$_SERVER['PHP_SELF']}/index.php/project/{$rowav["project"]}/issue/{$rowav["issue_id"]}";
// personalize body
$pb = str_ireplace(array("[[greeting]]","[[creator]]","[[action]]","[[project]]","[[issue_title]]","[[issue_body]]","[[comment]]","[[link]]"),
array($rowp["greeting"],$rowav["creator"],$rowav["description"],$rowav["name"],$rowav["title"],$rowav["body"],$rowav["comment"],$link),
$email_body);
mail($rowp['email'],"Bugs Activity...",$pb,$headers);
}
// mark event as notified
$db->query("update users_activity set notification_sent='Y' where id={$rowav['id']}");
}
?>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment