application/models/scheduled_reports.php

   1 <?php defined('SYSPATH') OR die('No direct access allowed.');
   2
   3 /**
   4  * Model for scheduled reports
   5  */
   6 class Scheduled_reports_Model extends Model
   7 {
   8         const USERFIELD = 'username'; /**< Name of username column in database */
   9
  10         /**
  11          * Given a scheduled report id, delet it from db
  12          */
  13         static function delete_scheduled_report($id=false)
  14         {
  15                 $id = (int)$id;
  16                 if (empty($id)) return false;
  17                 $sql = "DELETE FROM scheduled_reports WHERE id=".$id;
  18                 $db = Database::instance();
  19                 $db->query($sql);
  20                 return true;
  21         }
  22
  23         /**
  24         *       Delete ALL schedules for a certain report_id and type
  25         */
  26         static function delete_all_scheduled_reports($type='avail',$id=false)
  27         {
  28                 $type = strtolower($type);
  29                 $db = Database::instance();
  30
  31                 # what report_type_id do we have?
  32                 $sql = "SELECT id FROM scheduled_report_types WHERE identifier=".$db->escape($type);
  33                 $res = $db->query($sql);
  34                 if (!count($res))
  35                         return false;
  36                         # bail out if we can't find report_type
  37
  38                 $row = $res->current();
  39                 $report_type_id = $row->id;
  40                 $sql = "DELETE FROM scheduled_reports WHERE report_type_id=".$report_type_id." AND report_id=".$id;
  41                 try {
  42                         $db->query($sql);
  43                 } catch (Kohana_Database_Exception $e) {
  44                         return false;
  45                 }
  46                 return true;
  47         }
  48
  49         /**
  50          * Fetches all scheduled reports of current report type (avail/sla)
  51          *
  52          * @param $type string: {avail, sla, summary}
  53          * @return res
  54          */
  55         public static function get_scheduled_reports($type)
  56         {
  57                 $type = strtolower($type);
  58
  59                 $db = Database::instance();
  60
  61                 $sql_xtra = '';
  62                 $auth = op5auth::instance();
  63                 if (!$auth->authorized_for('host_view_all')) {
  64                         $sql_xtra = ' AND sr.'.self::USERFIELD.'='.$db->escape(Auth::instance()->get_user()->username).' ';
  65                 }
  66
  67                 $sql = "SELECT
  68                                 sr.*,
  69                                 rp.periodname,
  70                                 r.report_name AS reportname
  71                         FROM
  72                                 scheduled_reports sr,
  73                                 scheduled_report_types rt,
  74                                 scheduled_report_periods rp,
  75                                 saved_reports r
  76                         WHERE
  77                                 rt.identifier='".$type."' AND
  78                                 sr.report_type_id=rt.id AND
  79                                 rp.id=sr.period_id AND
  80                                 sr.report_id=r.id".$sql_xtra."
  81                         ORDER BY
  82                                 reportname";
  83
  84                 $res = $db->query($sql);
  85                 return $res;
  86         }
  87
  88         /**
  89          * Checks if a report is scheduled in autoreports
  90          *
  91          * @param $type string: {avail, sla}
  92          * @param $id int The report id
  93          * @return Array on success. False on error.
  94          */
  95         static function report_is_scheduled($type='avail', $id=false)
  96         {
  97                 $type = strtolower($type);
  98
  99                 $id = (int)$id;
 100                 if (!$id) return false;
 101                 $res = self::get_scheduled_reports($type);
 102                 if (!$res || count($res)==0) {
 103                         return false;
 104                 }
 105                 $return = false;
 106                 $res->result(false);
 107                 foreach ($res as $row) {
 108                         if ($row['report_id'] == $id) {
 109                                 $return[] = $row;
 110                         }
 111                 }
 112                 return $return;
 113         }
 114
 115         /**
 116          * Get available report periods
 117          * @return array [id] => string. False on errors.
 118          */
 119         static function get_available_report_periods()
 120         {
 121                 $sql = "SELECT * from scheduled_report_periods";
 122                 $db = Database::instance();
 123                 $res = $db->query($sql);
 124                 if(!$res || count($res)==0) {
 125                         return false;
 126                 }
 127
 128                 $periods = array();
 129                 foreach ($res as $period_row) {
 130                         $periods[$period_row->id] = $period_row->periodname;
 131                 }
 132                 return $periods;
 133         }
 134
 135         /**
 136          * Retrieves the value of a db field for a report id
 137          * @param $type the database column
 138          * @param $id the id of the scheduled report
 139          */
 140         static function fetch_scheduled_field_value($type=false, $id=false)
 141         {
 142                 $id = (int)$id;
 143                 $type = trim($type);
 144                 if (empty($type) || empty($id)) return false;
 145                 $sql = "SELECT $type FROM scheduled_reports WHERE id=".$id;
 146                 $db = Database::instance();
 147                 $res = $db->query($sql);
 148                 if (!$res || count($res) == 0) {
 149                         return false;
 150                 }
 151                 $row = $res->current();
 152                 return $row->{$type};
 153         }
 154
 155         /**
 156          * @param $id = false
 157          * @param $rep_type = false
 158          * @param $saved_report_id = false
 159          * @param $period = false
 160          * @param $recipients = false comma separated
 161          * @param $filename = ''
 162          * @param $description = ''
 163          * @param $local_persistent_filepath = ''
 164          * @param $attach_description = ''
 165          * @return string|int either error string or the report's id
 166          */
 167         static public function edit_report($id=false, $rep_type=false, $saved_report_id=false, $period=false, $recipients=false, $filename='', $description='', $local_persistent_filepath = '', $attach_description = 0)
 168         {
 169                 $local_persistent_filepath = trim($local_persistent_filepath);
 170                 if($local_persistent_filepath && !is_writable(rtrim($local_persistent_filepath, '/').'/')) {
 171                         return _("File path '$local_persistent_filepath' is not writable");
 172                 }
 173                 $db = Database::instance();
 174                 $id = (int)$id;
 175                 $rep_type = (int)$rep_type;
 176                 $saved_report_id = (int)$saved_report_id;
 177                 $period = (int)$period;
 178                 $recipients = trim($recipients);
 179                 $filename = trim($filename);
 180                 $description = trim($description);
 181                 $attach_description = (int) $attach_description;
 182                 $user = Auth::instance()->get_user()->username;
 183
 184                 if (!$rep_type || !$saved_report_id || !$period || empty($recipients)) return _('Missing data');
 185
 186                 // some users might use ';' to separate email adresses
 187                 // just replace it with ',' and continue
 188                 $recipients = str_replace(';', ',', $recipients);
 189                 $rec_arr = explode(',', $recipients);
 190                 if (!empty($rec_arr)) {
 191                         foreach ($rec_arr as $recipient) {
 192                                 if (trim($recipient)!='') {
 193                                         $checked_recipients[] = trim($recipient);
 194                                 }
 195                         }
 196                         $recipients = implode(', ', $checked_recipients);
 197                 }
 198
 199                 if ($id) {
 200                         // UPDATE
 201                         $sql = "UPDATE scheduled_reports SET ".self::USERFIELD."=".$db->escape($user).", report_type_id=".$rep_type.", report_id=".$saved_report_id.", recipients=".$db->escape($recipients).", period_id=".$period.", filename=".$db->escape($filename).", description=".$db->escape($description).", local_persistent_filepath = ".$db->escape($local_persistent_filepath).", attach_description = ".$db->escape($attach_description)." WHERE id=".$id;
 202                 } else {
 203                         $sql = "INSERT INTO scheduled_reports (".self::USERFIELD.", report_type_id, report_id, recipients, period_id, filename, description, local_persistent_filepath, attach_description)
 204                                 VALUES(".$db->escape($user).", ".$rep_type.", ".$saved_report_id.", ".$db->escape($recipients).", ".$period.", ".$db->escape($filename).", ".$db->escape($description).", ".$db->escape($local_persistent_filepath).", ".$db->escape($attach_description).")";
 205                 }
 206
 207                 try {
 208                         $res = $db->query($sql);
 209                 } catch (Kohana_Database_Exception $e) {
 210                         return _('DATABASE ERROR').": {$e->getMessage()}; $sql";
 211                 }
 212
 213                 if (!$id) {
 214                         $id = $res->insert_id();
 215                 }
 216                 return $id;
 217         }
 218
 219         /**
 220          * Update specific field for certain scheduled report
 221          * Called from reports_Controller::save_schedule_item() through ajax
 222          *
 223          * @param $id int: The id of the report.
 224          * @param $field string: The report field to update.
 225          * @param $value string: The new value.
 226          * @return true on succes. false on errors.
 227          */
 228         static function update_report_field($id=false, $field=false, $value=false)
 229         {
 230                 $id = (int)$id;
 231                 $field = trim($field);
 232                 $value = trim($value);
 233                 $db = Database::instance();
 234                 $sql = "UPDATE scheduled_reports SET ".$field."= ".$db->escape($value)." WHERE id=".$id;
 235                 try {
 236                         $res = $db->query($sql);
 237                 } catch (Kohana_Database_Exception $e) {
 238                         print $e->getMessage();
 239                         return false;
 240                 }
 241                 return true;
 242         }
 243
 244         /**
 245          * Get the type of a report.
 246          *
 247          * @param $id The id of the report.
 248          * @return Report type on success. False on errors.
 249          */
 250         static function get_typeof_report($id=false)
 251         {
 252                 $sql = "SELECT t.identifier FROM scheduled_reports sr, scheduled_report_types t WHERE ".
 253                         "sr.id=".(int)$id." AND t.id=sr.report_type_id";
 254                 $db = Database::instance();
 255                 try {
 256                         $res = $db->query($sql);
 257                 } catch (Kohana_Database_Exception $e) {
 258                         return false;
 259                 }
 260
 261                 return count($res)!=0 ? $res->current()->identifier : false;
 262         }
 263
 264         /**
 265          * Get the id of a named report
 266          *
 267          * @param $identifier string: The name of the report
 268          * @return False on errors. Id of the report on success.
 269          */
 270         static function get_report_type_id($identifier=false)
 271         {
 272                 $db = Database::instance();
 273                 $sql = "SELECT id FROM scheduled_report_types WHERE identifier=".$db->escape($identifier);
 274                 try {
 275                         $res = $db->query($sql);
 276                 } catch (Kohana_Database_Exception $e) {
 277                         return false;
 278                 }
 279
 280                 $id = false;
 281                 if (count($res)!=0) {
 282                         $res = $res->current();
 283                         $id = $res->id;
 284                 }
 285                 return $id;
 286         }
 287
 288         /**
 289         *       Fetch info on all defined report types, i.e all
 290         *       types we can schedule
 291         */
 292         static function get_all_report_types()
 293         {
 294                 $db = Database::instance();
 295                 $sql = "SELECT * FROM scheduled_report_types ORDER BY id";
 296                 $res = $db->query($sql);
 297                 return count($res) != 0 ? $res : false;
 298         }
 299
 300         /**
 301          * Fetch all info for a specific schedule.
 302          * This includes all relevant data about both schedule
 303          * and the report.
 304          *
 305          * @param $schedule_id The id of the schedule we're interested in.
 306          * @return False on errors. Array with scheduling information on success.
 307          */
 308         static function get_scheduled_data($schedule_id=false)
 309         {
 310                 $schedule_id = (int)$schedule_id;
 311                 if (!$schedule_id) {
 312                         return false;
 313                 }
 314
 315                 $sql = "SELECT sr.recipients, sr.filename, sr.local_persistent_filepath, sr.report_id FROM ".
 316                         "scheduled_reports sr ".
 317                         "WHERE sr.id=".$schedule_id;
 318                 $db = Database::instance();
 319                 $res = $db->query($sql)->result_array(false);
 320                 if (!$res)
 321                         return false;
 322                 return $res[0];
 323         }
 324
 325         /**
 326          * Fetch info on reports to be sent for specific
 327          * period (daily/weekly/monthly)
 328          *
 329          * @param $period_str string: { daily, weekly, monthly }
 330          * @return array
 331          */
 332         static function get_period_schedules($period_str)
 333         {
 334                 $period_str = trim(ucfirst($period_str));
 335                 $db = Database::instance();
 336
 337                 $sql = "SELECT r.id FROM scheduled_report_types rt
 338                         INNER JOIN scheduled_reports r ON rt.id=r.report_type_id
 339                         INNER JOIN scheduled_report_periods p ON r.period_id=p.id
 340                         WHERE p.periodname=".$db->escape($period_str);
 341                 $res = $db->query($sql);
 342                 return $res;
 343         }
 344 }