/** * Get machine group data for current user * * @author **/ function get_machine_groups() { $out = array(); if (isset($_SESSION['machine_groups'])) { // Get data for this unit $mg = new Machine_group(); foreach ($_SESSION['machine_groups'] as $group) { if ($mg_data = $mg->all($group)) { $out[] = $mg->all($group); } else { $out[] = array('name' => 'Group ' . $group, 'groupid' => $group); } } } else { $mg = new Machine_group(); $out = $mg->all(); } //Apply filter $groups = get_filtered_groups(); foreach ($out as &$group) { $group['checked'] = in_array($group['groupid'], $groups); } $obj = new View(); $obj->view('json', array('msg' => $out)); }
/** * Get filter for machine_group membership * * @var string optional prefix default 'WHERE' * @var string how to address the reportdata table - default 'reportdata' * @return string filter clause * @author **/ function get_machine_group_filter($prefix = 'WHERE', $reportdata = 'reportdata') { // Get filtered groups if ($groups = get_filtered_groups()) { return sprintf('%s %s.machine_group IN (%s)', $prefix, $reportdata, implode(', ', $groups)); } else { return ''; } }
/** * Retrieve all entries for serial * * @param string serial * @return array * @author abn290 **/ function fetch($cfg) { $dbh = getdbh(); // Initial value $recordsTotal = 0; // Output array $output = array("draw" => intval($cfg['draw']), "data" => array()); // Get tables from column names $tables = array(); // Add the reportdata table $tables['reportdata'] = 1; $formatted_columns = $columns = $search_cols = array(); foreach ($cfg['columns'] as $pos => $column) { $tbl_col_array = explode('.', $column['name']); if (count($tbl_col_array) == 2) { // Store table name $tables[$tbl_col_array[0]] = 1; // Format column name $formatted_columns[$pos] = sprintf('`%s`.`%s`', $tbl_col_array[0], $tbl_col_array[1]); } else { $formatted_columns[$pos] = sprintf('`%s`', $column['name']); } $columns[$pos] = $column['name']; // Check if search in column if (isset($column['search']['value']) && $column['search']['value']) { $search_cols[$pos] = $column['search']['value']; } } // Select $select = "SELECT " . implode(',', $formatted_columns); $tbl_list = array_keys($tables); // From $from = 'FROM ' . array_shift($tbl_list); // Join foreach ($tbl_list as $name) { $from .= " LEFT JOIN {$name} USING (serial_number)"; } // Where not empty $where = ''; if ($cfg['mrColNotEmpty']) { $where = sprintf('WHERE %s IS NOT NULL', $cfg['mrColNotEmpty']); } // Extra where clause (can only do is equal) if (is_array($cfg['where'])) { foreach ($cfg['where'] as $entry) { $my_where = sprintf("`%s`.`%s` = '%s'", $entry['table'], $entry['column'], $entry['value']); if ($where) { $where .= ' AND (' . $my_where . ')'; } else { $where = 'WHERE (' . $my_where . ')'; } } } // Business unit filter (assumes we are selecting the reportdata table) if ($machine_groups = get_filtered_groups()) { // Todo: We should check if a requested machine_group is allowed $bu_where = 'reportdata.machine_group IN (' . implode(', ', $machine_groups) . ')'; if ($where) { $where .= ' AND (' . $bu_where . ')'; } else { $where = 'WHERE (' . $bu_where . ')'; } } // Get total records $sql = "\n SELECT COUNT(1) as count\n {$from}\n {$where}"; //print $sql;return; if (!($stmt = $dbh->prepare($sql))) { $err = $dbh->errorInfo(); die($err[2]); } $stmt->execute(); // $bindings ); if ($rs = $stmt->fetch(PDO::FETCH_OBJ)) { $recordsTotal = $rs->count; } // Paging $sLimit = sprintf(' LIMIT %d,%d', $cfg['start'], $cfg['length']); // Show all if ($cfg['length'] == -1) { $sLimit = ''; } // Ordering $sOrder = ""; if (count($cfg['order'])) { $sOrder = "ORDER BY "; $order_arr = array(); foreach ($cfg['order'] as $order_entry) { $order_arr[] = sprintf('%s %s', $formatted_columns[$order_entry['column']], $order_entry['dir']); } $sOrder = "ORDER BY " . implode(',', $order_arr); } // Search $sWhere = $where; if ($cfg['search']) { $sWhere = $where ? $where . " AND (" : "WHERE ("; foreach ($formatted_columns as $col) { $sWhere .= $col . " LIKE '%" . $cfg['search'] . "%' OR "; } $sWhere = substr_replace($sWhere, "", -3); $sWhere .= ')'; } // Search columns if ($search_cols) { $sWhere = $where ? $where . " AND (" : "WHERE ("; foreach ($search_cols as $pos => $val) { if (is_string($val)) { if (preg_match('/([<>=] \\d+)|BETWEEN\\s+\\d+\\s+AND\\s+\\d+$/', $val)) { // Special case, use unquoted $compstr = $val; } else { // Regular string, quote $compstr = " = '{$val}'"; } } else { $compstr = " = {$val}"; } $sWhere .= $formatted_columns[$pos] . $compstr . " OR "; } $sWhere = substr_replace($sWhere, "", -3); $sWhere .= ')'; } // Get filtered results count $recordsFiltered = $recordsTotal; if ($sWhere) { $sql = "\n SELECT COUNT(*) as count\n {$from}\n {$sWhere}"; //print $sql;return; if (!($stmt = $dbh->prepare($sql))) { $err = $dbh->errorInfo(); die($err[2]); } $stmt->execute(); // $bindings ); if ($rs = $stmt->fetch(PDO::FETCH_OBJ)) { $recordsFiltered = $rs->count; } } $output["recordsTotal"] = $recordsTotal; $output["recordsFiltered"] = $recordsFiltered; $sql = "\n {$select}\n {$from}\n {$sWhere}\n {$sOrder}\n {$sLimit}\n "; //print $sql;return; // When in debug mode, send sql as well if (conf('debug')) { $output['sql'] = str_replace("\n", '', $sql); } if (!($stmt = $dbh->prepare($sql))) { $err = $dbh->errorInfo(); die($err[2]); } $stmt->execute(); // $bindings ); $arr = array(); while ($rs = $stmt->fetch(PDO::FETCH_NUM)) { $output['data'][] = $rs; } return $output; }