From 4af7dd7e10968588fe4c2828be5402f41d53ebc5 Mon Sep 17 00:00:00 2001
From: anuko <support@anuko.com>
Date: Tue, 25 Jul 2017 22:43:30 +0000
Subject: [PATCH] Initial implementation of a condition on notifications.

---
 WEB-INF/lib/common.lib.php                 | 17 +++++++++++++++++
 WEB-INF/lib/ttNotificationHelper.class.php | 10 ++++++----
 WEB-INF/lib/ttReportHelper.class.php       | 14 ++++++++++++++
 WEB-INF/lib/ttTeamHelper.class.php         |  2 +-
 WEB-INF/resources/en.lang.php              |  1 +
 WEB-INF/resources/ru.lang.php              |  1 +
 WEB-INF/templates/notification_add.tpl     |  4 ++++
 WEB-INF/templates/notification_edit.tpl    |  4 ++++
 WEB-INF/templates/notifications.tpl        |  2 ++
 cron.php                                   | 17 ++++++++++++-----
 dbinstall.php                              |  1 +
 mysql.sql                                  |  1 +
 notification_add.php                       |  4 ++++
 notification_edit.php                      |  7 ++++++-
 14 files changed, 74 insertions(+), 11 deletions(-)

diff --git a/WEB-INF/lib/common.lib.php b/WEB-INF/lib/common.lib.php
index 81c870f8..ac0b6825 100644
--- a/WEB-INF/lib/common.lib.php
+++ b/WEB-INF/lib/common.lib.php
@@ -308,6 +308,23 @@ function ttValidCronSpec($val)
   return true;
 }
 
+// ttValidCondition is used to check user input to validate a notification condition.
+function ttValidCondition($val, $emptyValid = true)
+{
+  $val = trim($val);
+  if (strlen($val) == 0)
+    return ($emptyValid ? true : false);
+
+  // String must not be XSS evil (to insert JavaScript).
+  if (stristr($val, '<script>') || stristr($val, '<script '))
+    return false;
+
+  if (!preg_match("/^count\s?>\s?\d+$/", $val))
+    return false;
+
+  return true;
+}
+
 // ttAccessCheck is used to check whether user is allowed to proceed. This function is used
 // as an initial check on all publicly available pages.
 function ttAccessCheck($required_rights)
diff --git a/WEB-INF/lib/ttNotificationHelper.class.php b/WEB-INF/lib/ttNotificationHelper.class.php
index f5a1603b..addbcdb6 100644
--- a/WEB-INF/lib/ttNotificationHelper.class.php
+++ b/WEB-INF/lib/ttNotificationHelper.class.php
@@ -39,7 +39,7 @@ class ttNotificationHelper {
  
     $mdb2 = getConnection();
 
-    $sql = "select c.id, c.cron_spec, c.report_id, c.email, c.status, fr.name from tt_cron c
+    $sql = "select c.id, c.cron_spec, c.report_id, c.email, c.report_condition, c.status, fr.name from tt_cron c
       left join tt_fav_reports fr on (fr.id = c.report_id)
       where c.id = $id and c.team_id = $user->team_id";
     $res = $mdb2->query($sql);
@@ -75,10 +75,11 @@ class ttNotificationHelper {
     $next = (int) $fields['next'];
     $report_id = (int) $fields['report_id'];
     $email = $fields['email'];
+    $report_condition = $fields['report_condition'];
     $status = $fields['status'];
     
-    $sql = "insert into tt_cron (team_id, cron_spec, next, report_id, email, status)
-      values ($team_id, ".$mdb2->quote($cron_spec).", $next, $report_id, ".$mdb2->quote($email).", ".$mdb2->quote($status).")";
+    $sql = "insert into tt_cron (team_id, cron_spec, next, report_id, email, report_condition, status)
+      values ($team_id, ".$mdb2->quote($cron_spec).", $next, $report_id, ".$mdb2->quote($email).", ".$mdb2->quote($report_condition).", ".$mdb2->quote($status).")";
     $affected = $mdb2->exec($sql);
     if (is_a($affected, 'PEAR_Error'))
       return false;
@@ -97,9 +98,10 @@ class ttNotificationHelper {
     $next = (int) $fields['next'];
     $report_id = (int) $fields['report_id'];
     $email = $fields['email'];
+    $report_condition = $fields['report_condition'];
     $status = $fields['status'];
     
-    $sql = "update tt_cron set cron_spec = ".$mdb2->quote($cron_spec).", next = $next, report_id = $report_id, email = ".$mdb2->quote($email).", status = ".$mdb2->quote($status).
+    $sql = "update tt_cron set cron_spec = ".$mdb2->quote($cron_spec).", next = $next, report_id = $report_id, email = ".$mdb2->quote($email).", report_condition = ".$mdb2->quote($report_condition).", status = ".$mdb2->quote($status).
       " where id = $notification_id and team_id = $team_id";
     $affected = $mdb2->exec($sql);
     return (!is_a($affected, 'PEAR_Error'));
diff --git a/WEB-INF/lib/ttReportHelper.class.php b/WEB-INF/lib/ttReportHelper.class.php
index 56a4aa80..71cee914 100644
--- a/WEB-INF/lib/ttReportHelper.class.php
+++ b/WEB-INF/lib/ttReportHelper.class.php
@@ -1300,6 +1300,20 @@ class ttReportHelper {
     return $body;
   }
 
+  // checkFavReportCondition - checks whether it is okay to send fav report.
+  static function checkFavReportCondition($report, $condition)
+  {
+    $items = ttReportHelper::getFavItems($report);
+
+    $condition = str_replace('count', '', $condition);
+    $count_required = intval(trim(str_replace('>', '', $condition)));
+
+    if (count($items) > $count_required)
+      return true; // Condition ok.
+
+    return false;
+  }
+
   // prepareFavReportBody - prepares an email body for a favorite report.
   static function prepareFavReportBody($report)
   {
diff --git a/WEB-INF/lib/ttTeamHelper.class.php b/WEB-INF/lib/ttTeamHelper.class.php
index e4e3b175..46a65e0d 100644
--- a/WEB-INF/lib/ttTeamHelper.class.php
+++ b/WEB-INF/lib/ttTeamHelper.class.php
@@ -520,7 +520,7 @@ class ttTeamHelper {
     $mdb2 = getConnection();
 
     $result = array();
-    $sql = "select c.id, c.cron_spec, c.email, fr.name from tt_cron c
+    $sql = "select c.id, c.cron_spec, c.email, c.report_condition, fr.name from tt_cron c
       left join tt_fav_reports fr on (fr.id = c.report_id)
       where c.team_id = $team_id and c.status is not null";
     $res = $mdb2->query($sql);
diff --git a/WEB-INF/resources/en.lang.php b/WEB-INF/resources/en.lang.php
index 0bb5d81f..38ae7fa6 100644
--- a/WEB-INF/resources/en.lang.php
+++ b/WEB-INF/resources/en.lang.php
@@ -185,6 +185,7 @@ $i18n_key_words = array(
 'label.role_comanager' => '(co-manager)',
 'label.role_admin' => '(administrator)',
 'label.page' => 'Page',
+'label.condition' => 'Condition',
 // Labels for plugins (extensions to Time Tracker that provide additional features).
 'label.custom_fields' => 'Custom fields',
 'label.monthly_quotas' => 'Monthly quotas',
diff --git a/WEB-INF/resources/ru.lang.php b/WEB-INF/resources/ru.lang.php
index 0c086b65..69def8e6 100644
--- a/WEB-INF/resources/ru.lang.php
+++ b/WEB-INF/resources/ru.lang.php
@@ -185,6 +185,7 @@ $i18n_key_words = array(
 'label.role_comanager' => '(ассистент менеджера)',
 'label.role_admin' => '(администратор)',
 'label.page' => 'Стр',
+'label.condition' => 'Условие',
 // Labels for plugins (extensions to Time Tracker that provide additional features).
 'label.custom_fields' => 'Дополнительные поля',
 'label.monthly_quotas' => 'Месячные квоты',
diff --git a/WEB-INF/templates/notification_add.tpl b/WEB-INF/templates/notification_add.tpl
index ff1d3e72..0cd37db1 100644
--- a/WEB-INF/templates/notification_add.tpl
+++ b/WEB-INF/templates/notification_add.tpl
@@ -15,6 +15,10 @@
           <td align="right">{$i18n.label.email} (*):</td>
           <td>{$forms.notificationForm.email.control}</td>
         </tr>
+        <tr>
+          <td align="right">{$i18n.label.condition}:</td>
+          <td>{$forms.notificationForm.report_condition.control} <a href="https://www.anuko.com/lp/tt_9.htm" target="_blank">{$i18n.label.what_is_it}</a></td>
+        </tr>
         <tr>
           <td height="40"></td>
           <td>{$i18n.label.required_fields}</td>
diff --git a/WEB-INF/templates/notification_edit.tpl b/WEB-INF/templates/notification_edit.tpl
index ad3a7db0..7080d0d9 100644
--- a/WEB-INF/templates/notification_edit.tpl
+++ b/WEB-INF/templates/notification_edit.tpl
@@ -15,6 +15,10 @@
           <td align="right">{$i18n.label.email} (*):</td>
           <td>{$forms.notificationForm.email.control}</td>
         </tr>
+        <tr>
+          <td align="right">{$i18n.label.condition}:</td>
+          <td>{$forms.notificationForm.report_condition.control} <a href="https://www.anuko.com/lp/tt_9.htm" target="_blank">{$i18n.label.what_is_it}</a></td>
+        </tr>
         <tr>
           <td height="40"></td>
           <td>{$i18n.label.required_fields}</td>
diff --git a/WEB-INF/templates/notifications.tpl b/WEB-INF/templates/notifications.tpl
index 7d51f52a..c9f43f2f 100644
--- a/WEB-INF/templates/notifications.tpl
+++ b/WEB-INF/templates/notifications.tpl
@@ -8,6 +8,7 @@
           <td class="tableHeader">{$i18n.label.thing_name}</td>
           <td class="tableHeader">{$i18n.label.cron_schedule}</td>
           <td class="tableHeader">{$i18n.label.email}</td>
+          <td class="tableHeader">{$i18n.label.condition}</td>
           <td class="tableHeader">{$i18n.label.edit}</td>
           <td class="tableHeader">{$i18n.label.delete}</td>
         </tr>
@@ -17,6 +18,7 @@
           <td>{$notification['name']|escape}</td>
           <td>{$notification['cron_spec']|escape}</td>
           <td>{$notification['email']|escape}</td>
+          <td>{$notification['report_condition']|escape}</td>
           <td><a href="notification_edit.php?id={$notification['id']}">{$i18n.label.edit}</a></td>
           <td><a href="notification_delete.php?id={$notification['id']}">{$i18n.label.delete}</a></td>
         </tr>
diff --git a/cron.php b/cron.php
index 7be547d6..d7825461 100644
--- a/cron.php
+++ b/cron.php
@@ -64,11 +64,18 @@ while ($val = $res->fetchRow()) {
   $user = new ttUser(null, $report['user_id']);
   $i18n->load($user->lang);
 
-  // Email report.
-  if (ttReportHelper::sendFavReport($report, $val['email']))
-    echo "Report ".$val['report_id']. " sent to ".$val['email']."<br>";
-  else
-    echo "Error while emailing report...<br>";
+  // Check condition on a report.
+  $condition_ok = true;
+  if ($val['report_condition'])
+    $condition_ok = ttReportHelper::checkFavReportCondition($report, $val['report_condition']);
+
+  // Email report if condition is okay.
+  if ($condition_ok) {
+    if (ttReportHelper::sendFavReport($report, $val['email']))
+      echo "Report ".$val['report_id']. " sent to ".$val['email']."<br>";
+    else
+      echo "Error while emailing report...<br>";
+  }
 
   // Calculate next execution time.
   $next = tdCron::getNextOccurrence($val['cron_spec'], $now + 60); // +60 sec is here to get us correct $next when $now is close to existing "next".
diff --git a/dbinstall.php b/dbinstall.php
index aea76e80..9638fe4f 100755
--- a/dbinstall.php
+++ b/dbinstall.php
@@ -638,6 +638,7 @@ if ($_POST) {
     setChange("CREATE TABLE `tt_predefined_expenses` (`id` int(11) NOT NULL auto_increment, `team_id` int(11) NOT NULL, `name` varchar(255) NOT NULL, `cost` decimal(10,2) default '0.00', PRIMARY KEY  (`id`))");
     setChange("ALTER TABLE `tt_teams` ADD `task_required` smallint(2) NOT NULL DEFAULT '0' AFTER `tracking_mode`");
     setChange("ALTER TABLE `tt_teams` ADD `project_required` smallint(2) NOT NULL DEFAULT '0' AFTER `tracking_mode`");
+    setChange("ALTER TABLE `tt_cron` ADD `report_condition` varchar(255) default NULL AFTER `email`");
   }
   
   // The update_clients function updates projects field in tt_clients table.
diff --git a/mysql.sql b/mysql.sql
index a9a88f64..bf0fb204 100644
--- a/mysql.sql
+++ b/mysql.sql
@@ -227,6 +227,7 @@ CREATE TABLE `tt_cron` (
   `next` int(11) default NULL,                  # UNIX timestamp of when to run next job
   `report_id` int(11) default NULL,             # report id from tt_fav_reports, a report to mail on schedule
   `email` varchar(100) default NULL,            # email to send results to
+  `report_condition` varchar(255) default NULL, # report condition, "count > 0" for sending not empty reports
   `status` tinyint(4) default '1',              # entry status
   PRIMARY KEY (`id`)
 );
diff --git a/notification_add.php b/notification_add.php
index 05b6ce0c..a12f6ff5 100644
--- a/notification_add.php
+++ b/notification_add.php
@@ -45,6 +45,7 @@ if ($request->isPost()) {
   $cl_fav_report = trim($request->getParameter('fav_report'));
   $cl_cron_spec = trim($request->getParameter('cron_spec'));
   $cl_email = trim($request->getParameter('email'));
+  $cl_report_condition = trim($request->getParameter('report_condition'));
 } else {
   $cl_cron_spec = '0 4 * * 1'; // Default schedule - weekly on Mondays at 04:00 (server time).
 }
@@ -60,6 +61,7 @@ $form->addInput(array('type'=>'combobox',
 ));
 $form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'cron_spec','style'=>'width: 250px;','value'=>$cl_cron_spec));
 $form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'email','style'=>'width: 250px;','value'=>$cl_email));
+$form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'report_condition','style'=>'width: 250px;','value'=>$cl_report_condition));
 $form->addInput(array('type'=>'submit','name'=>'btn_add','value'=>$i18n->getKey('button.add')));
 
 if ($request->isPost()) {
@@ -67,6 +69,7 @@ if ($request->isPost()) {
   if (!$cl_fav_report) $err->add($i18n->getKey('error.report'));
   if (!ttValidCronSpec($cl_cron_spec)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.cron_schedule'));
   if (!ttValidEmail($cl_email)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.email'));
+  if (!ttValidCondition($cl_report_condition)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.condition'));
 
   if ($err->no()) {
     // Calculate next execution time.
@@ -78,6 +81,7 @@ if ($request->isPost()) {
         'next' => $next,
         'report_id' => $cl_fav_report,
         'email' => $cl_email,
+        'report_condition' => $cl_report_condition,
         'status' => ACTIVE))) {
         header('Location: notifications.php');
         exit();
diff --git a/notification_edit.php b/notification_edit.php
index cd3f41bd..11a7ac0e 100644
--- a/notification_edit.php
+++ b/notification_edit.php
@@ -46,11 +46,13 @@ if ($request->isPost()) {
   $cl_fav_report = trim($request->getParameter('fav_report'));
   $cl_cron_spec = trim($request->getParameter('cron_spec'));
   $cl_email = trim($request->getParameter('email'));
+  $cl_report_condition = trim($request->getParameter('report_condition'));
 } else {
   $notification = ttNotificationHelper::get($notification_id);
   $cl_fav_report = $notification['report_id'];
   $cl_cron_spec = $notification['cron_spec'];
   $cl_email = $notification['email'];
+  $cl_report_condition = $notification['report_condition'];
 }
 
 $form = new Form('notificationForm');
@@ -64,6 +66,7 @@ $form->addInput(array('type'=>'combobox',
   'empty'=>array(''=>$i18n->getKey('dropdown.select'))));
 $form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'cron_spec','style'=>'width: 250px;','value'=>$cl_cron_spec));
 $form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'email','style'=>'width: 250px;','value'=>$cl_email));
+$form->addInput(array('type'=>'text','maxlength'=>'100','name'=>'report_condition','style'=>'width: 250px;','value'=>$cl_report_condition));
 $form->addInput(array('type'=>'submit','name'=>'btn_submit','value'=>$i18n->getKey('button.save')));
 
 if ($request->isPost()) {
@@ -71,6 +74,7 @@ if ($request->isPost()) {
   if (!$cl_fav_report) $err->add($i18n->getKey('error.report'));
   if (!ttValidCronSpec($cl_cron_spec)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.cron_schedule'));
   if (!ttValidEmail($cl_email)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.email'));
+  if (!ttValidCondition($cl_report_condition)) $err->add($i18n->getKey('error.field'), $i18n->getKey('label.condition'));
 
   if ($err->no()) {
     // Calculate next execution time.
@@ -83,6 +87,7 @@ if ($request->isPost()) {
         'next' => $next,
         'report_id' => $cl_fav_report,
         'email' => $cl_email,
+        'report_condition' => $cl_report_condition,
         'status' => ACTIVE))) {
         header('Location: notifications.php');
         exit();
@@ -92,6 +97,6 @@ if ($request->isPost()) {
 } // isPost
 
 $smarty->assign('forms', array($form->getName()=>$form->toArray()));
-$smarty->assign('title', $i18n->getKey('title.add_notification'));
+$smarty->assign('title', $i18n->getKey('title.edit_notification'));
 $smarty->assign('content_page_name', 'notification_edit.tpl');
 $smarty->display('index.tpl');
-- 
2.20.1