/**
  * Generate age data for age widget
  *
  * @author AvB
  **/
 function age()
 {
     // Authenticate
     if (!$this->authorized()) {
         die('Authenticate first.');
         // Todo: return json?
     }
     $out = array();
     $warranty = new Warranty_model();
     // Time calculations differ between sql implementations
     switch ($warranty->get_driver()) {
         case 'sqlite':
             $agesql = "CAST(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', purchase_date) AS INT)";
             break;
         case 'mysql':
             $agesql = "TIMESTAMPDIFF(YEAR,purchase_date,CURDATE())";
             break;
         default:
             // FIXME for other DB engines
             $agesql = "SUBSTR(purchase_date, 1, 4)";
     }
     // Get filter for business units
     $where = get_machine_group_filter();
     $sql = "SELECT count(1) as count, \n\t\t\t\t{$agesql} AS age \n\t\t\t\tFROM warranty\n\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\t{$where}\n\t\t\t\tGROUP by age \n\t\t\t\tORDER BY age DESC";
     $cnt = 0;
     foreach ($warranty->query($sql) as $obj) {
         $obj->age = $obj->age ? $obj->age : '<1';
         $out[] = array('label' => $obj->age, 'data' => array(array(intval($obj->count), $cnt++)));
     }
     $obj = new View();
     $obj->view('json', array('msg' => $out));
 }

 /**
  * Get statistics
  *
  * @return void
  * @author 
  **/
 function get_stats()
 {
     $now = time();
     $three_months = $now + 3600 * 24 * 30 * 3;
     $sql = "SELECT COUNT(1) as total, \n\t\t\tCOUNT(CASE WHEN cert_exp_time < '{$now}' THEN 1 END) AS expired, \n\t\t\tCOUNT(CASE WHEN cert_exp_time BETWEEN {$now} AND {$three_months} THEN 1 END) AS soon,\n\t\t\tCOUNT(CASE WHEN cert_exp_time > {$three_months} THEN 1 END) AS ok\n\t\t\tFROM certificate\n\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t" . get_machine_group_filter();
     return current($this->query($sql));
 }

 /**
  * Get statistics
  *
  * @return void
  * @author 
  **/
 function get_stats($hours)
 {
     $now = time();
     $today = $now - 3600 * 24;
     $week_ago = $now - 3600 * 24 * 7;
     $month_ago = $now - 3600 * 24 * 30;
     $sql = "SELECT COUNT(1) as total, \n\t\t\tCOUNT(CASE WHEN last_success > '{$today}' THEN 1 END) AS today, \n\t\t\tCOUNT(CASE WHEN last_success BETWEEN '{$week_ago}' AND '{$today}' THEN 1 END) AS lastweek,\n\t\t\tCOUNT(CASE WHEN last_success < '{$week_ago}' THEN 1 END) AS week_plus\n\t\t\tFROM crashplan\n\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t" . get_machine_group_filter();
     return current($this->query($sql));
 }

 /**
  * Select all entries
  *
  * @return array
  * @author 
  **/
 function select_all()
 {
     $sql = sprintf('SELECT name, version, COUNT(i.id) AS num_installs
         FROM %s i 
         LEFT JOIN reportdata r ON (r.serial_number = i.serial)
         %s 
         GROUP BY name, version', $this->enquote($this->tablename), get_machine_group_filter('WHERE', 'r'));
     return $this->query($sql);
 }

 /**
  * Get statistics 
  *
  * @return array
  * @author
  **/
 function get_stats($mountpoint = '/', $level1 = 5, $level2 = 10)
 {
     // Convert to GB
     $level1 = $level1 . '000000000';
     $level2 = $level2 . '000000000';
     $level2_minus_one = $level2 - 1;
     $sql = "SELECT COUNT(CASE WHEN FreeSpace > {$level2_minus_one} THEN 1 END) AS success,\n\t\t\t\t\t\tCOUNT(CASE WHEN FreeSpace < {$level2} THEN 1 END) AS warning,\n\t\t\t\t\t\tCOUNT(CASE WHEN FreeSpace < {$level1} THEN 1 END) AS danger\n\t\t\t\t\t\tFROM diskreport\n\t\t\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\t\t\tWHERE MountPoint = '{$mountpoint}'\n\t\t\t\t\t\t" . get_machine_group_filter('AND');
     return current($this->query($sql));
 }

 /**
  * Get devices with low battery
  *
  * Select devices with battery level below 15%
  *
  **/
 public function get_low()
 {
     $out = array();
     $sql = "SELECT bluetooth.serial_number, machine.computer_name,\n\t\t\t\t\t\t\tbluetooth.keyboard_battery, bluetooth.mouse_battery,\n\t\t\t\t\t\t\tbluetooth.trackpad_battery\n\t\t\t\t\t\tFROM bluetooth\n\t\t\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\t\t\tLEFT JOIN machine USING (serial_number)\n\t\t\t\t\t\tWHERE ((bluetooth.keyboard_battery BETWEEN 0 AND 14)\n\t\t\t\t\t\t\tOR (bluetooth.mouse_battery BETWEEN 0 AND 14)\n\t\t\t\t\t\t\tOR (bluetooth.trackpad_battery BETWEEN 0 AND 14))\n\t\t\t\t\t\t" . get_machine_group_filter('AND');
     foreach ($this->query($sql) as $obj) {
         $out[] = $obj;
     }
     return $out;
 }

 /**
  * Retrieve all tags
  *
  *
  **/
 public function all_tags()
 {
     $out = array();
     $filter = get_machine_group_filter();
     $sql = "SELECT tag\n\t\t\tFROM tag\n\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t{$filter}\n\t\t\tGROUP BY tag\n            ORDER BY tag ASC";
     foreach ($this->query($sql) as $obj) {
         $out[] = $obj->tag;
     }
     return $out;
 }

 /**
  * Get conditions
  *
  * @return void
  * @author AvB
  **/
 function conditions()
 {
     if (!$this->authorized()) {
         die('Authenticate first.');
         // Todo: return json
     }
     $queryobj = new Power_model();
     $sql = "SELECT COUNT(CASE WHEN `condition` = 'Normal' THEN 1 END) AS normal,\n\t\t\t\t\t\tCOUNT(CASE WHEN `condition` = 'Replace Soon' THEN 1 END) AS soon,\n\t\t\t\t\t\tCOUNT(CASE WHEN `condition` = 'Service Battery' THEN 1 END) AS service,\n\t\t\t\t\t\tCOUNT(CASE WHEN `condition` = 'Replace Now' THEN 1 END) AS now,\n\t\t\t\t\t\tCOUNT(CASE WHEN `condition` = 'No Battery' THEN 1 END) AS missing\n\t\t\t \t\t\tFROM power\n\t\t\t \t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t \t\t\t" . get_machine_group_filter();
     $obj = new View();
     $obj->view('json', array('msg' => current($queryobj->query($sql))));
 }

 /**
  * Retrieve locations and names to render on a google map
  *
  *
  **/
 public function get_map_data()
 {
     // FIXME Does not account for business units!!!
     $out = array();
     $filter = get_machine_group_filter();
     $sql = "SELECT serial_number, latitude, longitude, long_username,\n\t\t\t\t\tcomputer_name\n\t\t\t\tFROM location\n\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\tLEFT JOIN machine USING (serial_number)\n\t\t\t\t{$filter}\n\t\t\t\tAND currentstatus = 'Successful'";
     foreach ($this->query($sql) as $obj) {
         $out[] = $obj;
     }
     return $out;
 }

 /**
  * Get statistics
  *
  * @return array
  * @author
  **/
 function get_groups($type)
 {
     $out = array();
     $sql = "SELECT {$type}, COUNT(1) AS count\n                    FROM site_info\n                    LEFT JOIN reportdata USING (serial_number)\n                    " . get_machine_group_filter() . "\n                    GROUP BY {$type}\n                    ORDER BY COUNT DESC";
     $stmt = $this->prepare($sql);
     $this->execute($stmt);
     while ($rs = $stmt->fetch(PDO::FETCH_OBJ)) {
         $out[] = array($type => $rs->{$type}, 'count' => $rs->count);
     }
     return $out;
 }

 /**
  * Get printer names for widget
  *
  **/
 public function get_printers()
 {
     $out = array();
     $sql = "SELECT COUNT(1) AS count, name \n\t\t\t\t    FROM printer\n\t\t\t\t    LEFT JOIN reportdata USING (serial_number)\n                    " . get_machine_group_filter() . "\n                    GROUP BY name\n                    ORDER BY count DESC";
     foreach ($this->query($sql) as $obj) {
         if ("{$obj->count}" !== "0") {
             $obj->name = $obj->name ? $obj->name : 'Unknown';
             $out[] = $obj;
         }
     }
     return $out;
 }

 /**
  * Get WiFi names for widget
  *
  **/
 public function get_wifi_name()
 {
     $out = array();
     $sql = "SELECT COUNT(CASE WHEN ssid <> '' AND ssid IS NOT NULL THEN 1 END) AS count, ssid \n                FROM wifi\n                LEFT JOIN reportdata USING (serial_number)\n                " . get_machine_group_filter() . "\n                GROUP BY ssid\n                ORDER BY count DESC";
     foreach ($this->query($sql) as $obj) {
         if ("{$obj->count}" !== "0") {
             $obj->ssid = $obj->ssid ? $obj->ssid : 'Unknown';
             $out[] = $obj;
         }
     }
     return $out;
 }

 /**
  * Get Event
  *
  * @author AvB
  **/
 function get($minutes = 60, $type = 'all', $module = 'all')
 {
     $queryobj = new Event_model();
     $queryobj = new Reportdata_model();
     $fromtime = time() - 60 * $minutes;
     $out['items'] = array();
     $out['error'] = '';
     $sql = "SELECT m.serial_number, module, type, msg, data, m.timestamp,\n\t\t\t\t\tmachine.computer_name\n\t\t\t\tFROM event m \n\t\t\t\tLEFT JOIN reportdata USING (serial_number) \n\t\t\t\tLEFT JOIN machine USING (serial_number) \n\t\t\t\tWHERE m.timestamp > {$fromtime} \n\t\t\t\t" . get_machine_group_filter('AND') . "\n\t\t\t\tORDER BY m.timestamp DESC";
     foreach ($queryobj->query($sql) as $obj) {
         $out['items'][] = $obj;
     }
     $obj = new View();
     $obj->view('json', array('msg' => $out));
 }

 /**
  * Get versions and count from an application
  *
  * @param string $app Appname
  **/
 public function appVersions($app = '')
 {
     // Detect wildcard character
     $match = 'AND i.name = ?';
     if (preg_match('/[_%]/', $app)) {
         $match = 'AND i.name LIKE ?';
     }
     $sql = sprintf('SELECT version, COUNT(i.id) AS count
         FROM %s i 
         LEFT JOIN reportdata r ON (r.serial_number = i.serial_number)
         %s 
         %s
         GROUP BY version
         ORDER BY count DESC', $this->enquote($this->tablename), get_machine_group_filter('WHERE', 'r'), $match);
     return $this->query($sql, $app);
 }

 /**
  * REST interface, returns json with ip address ranges
  * defined in conf('ipv4router')
  * or passed with GET request
  *
  * @return void
  * @author AvB
  **/
 function routers()
 {
     if (!$this->authorized()) {
         die('Authenticate first.');
         // Todo: return json?
     }
     $router_arr = array();
     // See if we're being parsed a request object
     if (array_key_exists('req', $_GET)) {
         $router_arr = (array) json_decode($_GET['req']);
     }
     if (!$router_arr) {
         $router_arr = conf('ipv4routers', array());
     }
     $out = array();
     $reportdata = new Reportdata_model();
     // Compile SQL
     $cnt = 0;
     $sel_arr = array('COUNT(1) as count');
     foreach ($router_arr as $key => $value) {
         if (is_scalar($value)) {
             $value = array($value);
         }
         $when_str = '';
         foreach ($value as $k => $v) {
             $when_str .= sprintf(" WHEN ipv4router LIKE '%s%%' THEN 1", $v);
         }
         $sel_arr[] = "SUM(CASE {$when_str} ELSE 0 END) AS r{$cnt}";
         $cnt++;
     }
     $sql = "SELECT " . implode(', ', $sel_arr) . " FROM network\n\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\tWHERE ipv4router != '(null)' AND ipv4router != ''" . get_machine_group_filter('AND');
     // Create Out array
     if ($obj = current($reportdata->query($sql))) {
         $cnt = $total = 0;
         foreach ($router_arr as $key => $value) {
             $col = 'r' . $cnt++;
             $out[] = array('key' => $key, 'cnt' => intval($obj->{$col}));
             $total += $obj->{$col};
         }
         // Add Remaining IP's as other
         if ($obj->count - $total) {
             $out[] = array('key' => 'Other', 'cnt' => $obj->count - $total);
         }
     }
     $obj = new View();
     $obj->view('json', array('msg' => $out));
 }

 /**
  * Get Warranty statistics
  *
  **/
 public function get_stats($alert = FALSE)
 {
     $out = array();
     $filter = get_machine_group_filter();
     $datefilter = '';
     // Check if we have to only return machines due in 30 days
     if ($alert) {
         $thirtydays = date('Y-m-d', strtotime('+30days'));
         $yesterday = date('Y-m-d', strtotime('-1day'));
         $datefilter = "AND (end_date BETWEEN '{$yesterday}' AND '{$thirtydays}')";
     }
     $sql = "SELECT count(*) AS count, status\n\t\t\t\t\tFROM warranty\n\t\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\t\t{$filter}\n\t\t\t\t\t{$datefilter}\n\t\t\t\t\tGROUP BY status\n\t\t\t\t\tORDER BY count DESC";
     foreach ($this->query($sql) as $obj) {
         $out[] = $obj;
     }
     return $out;
 }

 /**
  * Get statistics
  *
  * Get object describing statistics
  *
  * @param integer hours hours of statistics
  **/
 public function get_stats($hours = 24)
 {
     $timestamp = date('Y-m-d H:i:s', time() - 60 * 60 * $hours);
     $sql = "SELECT \n\t\t\tSUM(errors > 0) as error, \n\t\t\tSUM(warnings > 0) as warning, \n\t\t\tSUM(pendinginstalls > 0) as pending,\n\t\t\tSUM(installresults > 0) as installed \n\t\t\tFROM munkireport\n\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t" . get_machine_group_filter() . "\n\t\t\tAND munkireport.timestamp > '{$timestamp}'";
     return current($this->query($sql));
 }

			<thead>
			  <tr>
			  	<th>Client</th>
			  	<th>Serial</th>
			  	<th>User</th>
			  	<th>IP</th>
			  	<th>Latest run</th>
			    <th>Name</th>
				<th>Type</th>
			  </tr>
			</thead>

			<tbody>
				<?php 
$sql = "SELECT computer_name, \n\t\t\t\t\t\t\tm.serial_number,\n\t\t\t\t\t\t\tlong_username,\n\t\t\t\t\t\t\tm.timestamp,\n\t\t\t\t\t\t\tremote_ip,\n\t\t\t\t\t\t\treport_plist\n\t\t\t\t\t\tFROM munkireport m\n\t\t\t\t\t\tLEFT JOIN machine USING (serial_number)\n\t\t\t\t\t\tLEFT JOIN reportdata USING (serial_number)\n\t\t\t\t\t\tWHERE pendinginstalls > 0\n\t\t\t\t\t\t" . get_machine_group_filter('AND');
$compress = function_exists('gzdeflate');
$mr = new Munkireport_model();
?>
				<?php 
foreach ($mr->query($sql) as $obj) {
    ?>
					<?php 
    $report_plist = unserialize($compress ? gzinflate($obj->report_plist) : $obj->report_plist);
    ?>
					<?php 
    if (isset($report_plist['AppleUpdates'])) {
        ?>
						<?php 
        foreach ($report_plist['AppleUpdates'] as $update) {
            ?>

				<div class="panel-heading">

					<h3 class="panel-title"><i class="fa fa-umbrella"></i> Warranty status</h3>
				
				</div>

				<div class="list-group">

				<?php 
$warranty = new Warranty_model();
$thirtydays = date('Y-m-d', strtotime('+30days'));
$yesterday = date('Y-m-d', strtotime('-1day'));
$class_list = array('Supported' => 'warning');
$cnt = 0;
$filter = get_machine_group_filter('AND');
$sql = "SELECT count(1) AS count, status \n\t\t\t\t\t\t\t\tFROM warranty\n\t\t\t\t\t\t\t\tLEFT JOIN reportdata USING(serial_number)\n\t\t\t\t\t\t\t\tWHERE (end_date BETWEEN '{$yesterday}' AND '{$thirtydays}') \n\t\t\t\t\t\t\t\tAND status != 'Expired'\n\t\t\t\t\t\t\t\t{$filter}\n\t\t\t\t\t\t\t\tGROUP BY status \n\t\t\t\t\t\t\t\tORDER BY count DESC";
?>
					<?php 
foreach ($warranty->query($sql) as $obj) {
    ?>
					<?php 
    $status = array_key_exists($obj->status, $class_list) ? $class_list[$obj->status] : 'danger';
    ?>
					<a href="<?php 
    echo url('show/listing/warranty#' . $obj->status);
    ?>
" class="list-group-item list-group-item-<?php 
    echo $status;
    ?>
">

 /**
  * Get check-in statistics
  *
  *
  **/
 public function get_lastseen_stats()
 {
     $now = time();
     $hour_ago = $now - 3600;
     $today = strtotime('today');
     $week_ago = $now - 3600 * 24 * 7;
     $month_ago = $now - 3600 * 24 * 30;
     $three_month_ago = $now - 3600 * 24 * 90;
     $sql = "SELECT COUNT(1) as total, \n        \tCOUNT(CASE WHEN timestamp > {$hour_ago} THEN 1 END) AS lasthour, \n        \tCOUNT(CASE WHEN timestamp > {$today} THEN 1 END) AS today, \n        \tCOUNT(CASE WHEN timestamp > {$week_ago} THEN 1 END) AS lastweek,\n        \tCOUNT(CASE WHEN timestamp > {$month_ago} THEN 1 END) AS lastmonth,\n        \tCOUNT(CASE WHEN timestamp BETWEEN {$month_ago} AND {$week_ago} THEN 1 END) AS inactive_week,\n        \tCOUNT(CASE WHEN timestamp BETWEEN {$three_month_ago} AND {$month_ago} THEN 1 END) AS inactive_month,\n        \tCOUNT(CASE WHEN timestamp < {$three_month_ago} THEN 1 END) AS inactive_three_month\n        \tFROM reportdata\n        \t" . get_machine_group_filter();
     return current($this->query($sql));
 }