$query .= $conditionAll . " " . getOrderBy('entity', $columns); $result = $DB->query($query); for ($row_num = 0; $data = $DB->fetch_assoc($result); $row_num++) { $dataAll[$row_num] = $data; } //case employment $queryEmploy = "SELECT `glpi_plugin_resources_employments`.`entities_id` AS entity,\n 'Employment' AS typeName,\n `glpi_plugin_resources_employments`.`name` AS name,\n `glpi_plugin_resources_employments`.`id` AS ID,\n NULL AS firstname,\n NULL AS registration_number,\n `glpi_plugin_resources_professions`.`id` AS profID,\n `glpi_plugin_resources_professions`.`name` AS profession,\n `glpi_plugin_resources_employments`.`begin_date` AS date_begin,\n `glpi_plugin_resources_employments`.`end_date` AS date_end,\n `glpi_plugin_resources_professions`.`begin_date`,\n `glpi_plugin_resources_professions`.`end_date`\n FROM `glpi_plugin_resources_employments`\n LEFT JOIN `glpi_plugin_resources_professions`\n ON (`glpi_plugin_resources_employments`.`plugin_resources_professions_id` = `glpi_plugin_resources_professions`.`id`)\n WHERE ((`glpi_plugin_resources_employments`.`begin_date` < '" . $date . "'\n AND (`glpi_plugin_resources_employments`.`end_date` IS NULL\n OR `glpi_plugin_resources_employments`.`end_date` > '" . $date . "'))\n AND `glpi_plugin_resources_professions`.`id` IS NOT NULL)\n AND ((`glpi_plugin_resources_employments`.`begin_date` > `glpi_plugin_resources_professions`.`end_date`\n OR `glpi_plugin_resources_employments`.`end_date` < `glpi_plugin_resources_professions`.`begin_date`)\n OR (`glpi_plugin_resources_professions`.`end_date` < '" . $date . "'\n OR `glpi_plugin_resources_professions`.`begin_date` > '" . $date . "'))"; // WHERE ((`glpi_plugin_resources_employments`.`begin_date` > `glpi_plugin_resources_ranks`.`end_date` // OR `glpi_plugin_resources_employments`.`end_date` < `glpi_plugin_resources_professions`.`begin_date` // OR `glpi_plugin_resources_employments`.`begin_date` < '".$date."') // AND `glpi_plugin_resources_employments`.`end_date` IS NULL // AND (`glpi_plugin_resources_professions`.`end_date` < '".$date."' // OR `glpi_plugin_resources_professions`.`begin_date` > '".$date."')) // AND `glpi_plugin_resources_professions`.`id` IS NOT NULL)"; $conditionAll = getEntitiesRestrictRequest('AND', 'glpi_plugin_resources_employments', '', '', true); $queryEmploy .= $conditionAll . " " . getOrderBy('entity', $columns); foreach ($DB->request($queryEmploy) as $dataEmploy) { $dataAll[$row_num] = $dataEmploy; $row_num++; } $nbtot = count($dataAll); if ($limit) { $start = isset($_GET["start"]) ? $_GET["start"] : 0; if ($start >= $nbtot) { $start = 0; } } else { $start = 0; } if ($nbtot == 0) { if (!$HEADER_LOADED) {
if ($res && $nbtot > 0) { $nbCols = $DB->num_fields($res); $nbrows = $DB->numrows($res); $num = 1; $link = $_SERVER['PHP_SELF']; $order = 'ASC'; $issort = false; echo Search::showHeader($output_type, $nbrows, $nbCols, true); echo Search::showNewLine($output_type); showTitle($output_type, $num, __('Entity'), 'entity', true); showTitle($output_type, $num, __('Service'), 'groups_id', true); showTitle($output_type, $num, PluginTypologyTypology::getTypeName(1), 'typoID', true); showTitle($output_type, $num, __('Number', 'typology'), 'COUNT', true); echo Search::showEndLine($output_type); //By service and typology $queryService = "SELECT count(*) AS COUNT,\n `glpi_plugin_typology_typologies`.`entities_id` AS entity,\n `glpi_computers`.`groups_id`,\n `glpi_plugin_typology_typologies`.`id` AS typoID\n FROM `glpi_plugin_typology_typologies_items`\n LEFT JOIN `glpi_plugin_typology_typologies`\n ON(`glpi_plugin_typology_typologies_items`.`plugin_typology_typologies_id`\n = `glpi_plugin_typology_typologies`.`id`)\n LEFT JOIN `glpi_computers`\n ON (`glpi_plugin_typology_typologies_items`.`items_id` = `glpi_computers`.`id`\n AND `glpi_plugin_typology_typologies_items`.`itemtype` = 'Computer')\n LEFT JOIN `glpi_entities`\n ON (`glpi_plugin_typology_typologies`.`entities_id` = `glpi_entities`.`id`)\n WHERE {$condition} {$sqltypo}\n GROUP BY `glpi_entities`.`entities_id`,\n `glpi_computers`.`groups_id`,\n `glpi_plugin_typology_typologies`.`id`" . getOrderBy('entity', $columns); $resultService = $DB->query($queryService); $row_num = 1; while ($dataService = $DB->fetch_assoc($resultService)) { if ($dataService['groups_id'] == 0) { $serviceName = __('None'); } else { $serviceName = Dropdown::getDropdownName("glpi_groups", $dataService['groups_id']); } if ($dataService['typoID'] == '0') { $typoName = __('None'); } else { $typoName = Dropdown::getDropdownName("glpi_plugin_typology_typologies", $dataService["typoID"]); } $entityName = Dropdown::getDropdownName('glpi_entities', $dataService['entity']); $row_num++;
function getOrderBy() { return getOrderBy($this); }
function find($inFindCol, $inFind = '', $inOb = '', $inFindFrom = 0, $inFindForward = true, $inFullText = true) { global $table; // build order by clause $desc = (bool) getPostString("desc"); if (!$inOb) { $inOb = getOrderBy(); } if ($inOb) { $inOb = "`" . $inOb . "`"; } $orderby = $inOb ? " ORDER BY {$inOb} " . ($desc ? " DESC" : "") : ""; // update inputs from post if (!$inFind) { $inFind = getPostString('findText'); } if (!$inFindCol) { $inFindCol = getPostString('findCol'); } if (empty($inFindFrom)) { $inFindFrom = getPostString('findFrom'); } $ff = getPostString('findForward'); if ($ff) { $inFindForward = strtolower($ff) == 'true' ? true : false; } $ft = getPostString('findFullText'); if ($ft) { $inFullText = strtolower($ft) == 'true' ? true : false; } // get find data $f = findData($inFindCol, $inFind, $orderby, $inFullText); $pk = getPk(); // execute query $where = buildFindWhere($f, $pk->index, 'f'); $query = "SELECT Row, f FROM (SELECT @row := @row + 1 AS Row, {$pk->name} as f FROM `{$table}` {$orderby}) AS tempTable {$where}"; mysql_query('SET @row = -1;'); if (!($result = mysql_query($query))) { error("failed to perform query: {$query}. " . mysql_error()); } // return row number return findRow(getArray($result), $inFindFrom, $inFindForward); }
$result = $DB->query($query); for ($row_num = 0; $data = $DB->fetch_assoc($result); $row_num++) { if ($row_num == 0) { $dataAll[$row_num]['professionline'] = PluginResourcesProfession::getTypeName(1); $row_num++; } $dataAll[$row_num] = $data; } //Case of specific management for each grade of a profession $rankList = "SELECT DISTINCT(`glpi_plugin_resources_budgets`.`plugin_resources_ranks_id`)\n FROM `glpi_plugin_resources_budgets`\n WHERE ((`glpi_plugin_resources_budgets`.`begin_date` < '" . $date . "')\n AND (`glpi_plugin_resources_budgets`.`end_date` IS NOT NULL\n OR `glpi_plugin_resources_budgets`.`end_date` > '" . $date . "'))\n ORDER BY `glpi_plugin_resources_budgets`.`plugin_resources_ranks_id`"; foreach ($DB->request($rankList) as $d) { if ($d['plugin_resources_ranks_id'] != 0) { $rank = new PluginResourcesRank(); $rank->getFromDB($d['plugin_resources_ranks_id']); $qRank = "SELECT `glpi_plugin_resources_ranks`.`name` AS rank_name,\n `glpi_plugin_resources_ranks`.`code` AS rank_code,\n `glpi_plugin_resources_professions`.`plugin_resources_professionlines_id` AS professionline,\n `glpi_plugin_resources_professions`.`plugin_resources_professioncategories_id` AS professioncategory,\n `glpi_plugin_resources_professions`.`name` AS profession,\n `glpi_plugin_resources_professions`.`id` AS profession_id,\n `glpi_plugin_resources_professions`.`code` AS profession_code,\n `glpi_plugin_resources_professions`.`begin_date`,\n `glpi_plugin_resources_professions`.`end_date`\n FROM `glpi_plugin_resources_ranks`\n LEFT JOIN `glpi_plugin_resources_professions`\n ON (`glpi_plugin_resources_ranks`.`plugin_resources_professions_id` = `glpi_plugin_resources_professions`.`id`)\n LEFT JOIN `glpi_plugin_resources_budgets`\n ON (`glpi_plugin_resources_budgets`.`plugin_resources_ranks_id` = `glpi_plugin_resources_ranks`.`id`\n AND ((`glpi_plugin_resources_budgets`.`begin_date` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`begin_date` < '" . $date . "')\n AND (`glpi_plugin_resources_budgets`.`end_date` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`end_date` > '" . $date . "')))\n WHERE `glpi_plugin_resources_ranks`.`plugin_resources_professions_id`='" . $rank->getField('plugin_resources_professions_id') . "'\n AND `glpi_plugin_resources_ranks`.`is_active` = 1\n AND ((`glpi_plugin_resources_ranks`.`end_date` IS NULL )\n OR (`glpi_plugin_resources_ranks`.`end_date` > '" . $date . "' ))\n AND ((`glpi_plugin_resources_ranks`.`begin_date` IS NULL)\n OR ( `glpi_plugin_resources_ranks`.`begin_date` < '" . $date . "'))\n AND ((`glpi_plugin_resources_budgets`.`id` IS NULL)\n OR (`glpi_plugin_resources_budgets`.`begin_date` IS NOT NULL\n AND `glpi_plugin_resources_budgets`.`begin_date` > '" . $date . "')\n OR (`glpi_plugin_resources_budgets`.`end_date` IS NOT NULL\n AND `glpi_plugin_resources_budgets`.`end_date` < '" . $date . "')) " . $sqlprofessioncategory . $sqlprofessionline; $qRank .= $conditionAll . " " . getOrderBy('profession', $columns); $first = 0; foreach ($DB->request($qRank) as $dataRank) { if ($first == 0) { $dataAll[$row_num]['professionline'] = PluginResourcesRank::getTypeName(1); $first++; $row_num++; } $dataAll[$row_num] = $dataRank; $row_num++; } } } $nbtot = count($dataAll); if ($limit) { $start = isset($_GET["start"]) ? $_GET["start"] : 0;
function getRecords($con, $where, $orderby, $limit, $offset, $pagesize, &$rowcount) { $sql_where = getWhere($where); $sql_orderby = getOrderBy($orderby, $count); $sql_limit = getLimit($limit, 0); $sql_page = getLimit($pagesize, $offset); if ($limit) { if ($pagesize) { $sql = "SELECT * FROM phones " . $sql_where . $sql_orderby . $sql_limit; $sql = "select * from ( " . $sql . " ) a " . $sql_page; $records = fetchRows($con, $sql); if (!$records) { return false; } $rc_sql = "SELECT 1 FROM phones " . $sql_where . $sql_limit; $rc_sql = "select count(*) AS rowcount from ( " . $rc_sql . " ) rc"; $rowcount = fetchRowCount($con, $rc_sql); if (!$rowcount) { return false; } return $records; } $sql = "SELECT * FROM phones " . $sql_where . $sql_orderby . $sql_limit; $records = fetchRows($con, $sql); $rowcount = count($records); return $records; } if ($pagesize) { $sql = "SELECT * FROM phones " . $sql_where . $sql_orderby . $sql_page; $records = fetchRows($con, $sql); if (!$records) { return false; } $rc_sql = "SELECT 1 FROM phones " . $sql_where; $rc_sql = "select count(*) AS rowcount from ( " . $rc_sql . " ) rc"; $rowcount = fetchRowCount($con, $rc_sql); if (!$rowcount) { return false; } return $records; } $sql = "SELECT * FROM phones " . $sql_where . $sql_orderby; $records = fetchRows($con, $sql); if (!$records) { return false; } $rowcount = count($records); return $records; }
$output_type = $_POST["display_type"]; if ($output_type < 0) { $output_type = -$output_type; $limit = 0; } } //else { // $output_type = Search::HTML_OUTPUT; //} //Report title $title = $report->getFullTitle(); // SQL statement $query = "SELECT glpi_tickets.* \n FROM `glpi_tickets`\n WHERE `glpi_tickets`.`status` = '" . Ticket::CLOSED . "'"; $query .= getEntitiesRestrictRequest('AND', "glpi_tickets", '', '', false); $query .= $date->getSqlCriteriasRestriction(); $query .= getOrderBy('closedate', $columns); $res = $DB->query($query); $nbtot = $res ? $DB->numrows($res) : 0; if ($limit) { $start = isset($_GET["start"]) ? $_GET["start"] : 0; if ($start >= $nbtot) { $start = 0; } if ($start > 0 || $start + $limit < $nbtot) { $res = $DB->query($query . " LIMIT {$start},{$limit}"); } } else { $start = 0; } if ($nbtot == 0) { if (!$HEADER_LOADED) {
if ($output_type < 0) { $output_type = -$output_type; $limit = 0; } } else { $output_type = Search::HTML_OUTPUT; } $title = $report->getFullTitle(); //to verify if budget exist // SQL statement $condition = getEntitiesRestrictRequest('', "glpi_plugin_resources_budgets", '', '', false); $date = $datecrit->getDate(); $sqlprofessioncategory = $professioncategory->getSqlCriteriasRestriction('AND'); $sqlprofessionline = $professionline->getSqlCriteriasRestriction('AND'); //recover all budgets $query = "SELECT `glpi_plugin_resources_professions`.`plugin_resources_professioncategories_id` AS professioncategory,\n `glpi_plugin_resources_professions`.`plugin_resources_professionlines_id` AS professionline,\n `glpi_plugin_resources_budgets`.`plugin_resources_professions_id` AS profession,\n `glpi_plugin_resources_budgets`.`plugin_resources_ranks_id` AS rank,\n `glpi_plugin_resources_budgets`.`begin_date` AS begin_date,\n `glpi_plugin_resources_budgets`.`end_date` AS end_date,\n `glpi_plugin_resources_budgets`.`plugin_resources_budgettypes_id` AS budget_type,\n `glpi_plugin_resources_budgets`.`volume` AS qt_vol_budg_vot\n FROM `glpi_plugin_resources_budgets`\n LEFT JOIN `glpi_plugin_resources_professions`\n ON (`glpi_plugin_resources_budgets`.`plugin_resources_professions_id`\n = `glpi_plugin_resources_professions`.`id`)\n WHERE " . $condition . "\n AND (`glpi_plugin_resources_budgets`.`begin_date` <= '" . $date . "'\n AND (`glpi_plugin_resources_budgets`.`end_date` IS NULL\n OR `glpi_plugin_resources_budgets`.`end_date` >= '" . $date . "'))\n AND `glpi_plugin_resources_professions`.`is_active` = 1\n AND ((`glpi_plugin_resources_professions`.`begin_date` <= '" . $date . "')\n AND (`glpi_plugin_resources_professions`.`end_date` IS NULL\n OR `glpi_plugin_resources_professions`.`end_date` >= '" . $date . "')) " . $sqlprofessioncategory . $sqlprofessionline . "\n GROUP BY profession,\n rank,\n budget_type " . getOrderBy('profession', $columns); $res = $DB->query($query); $nbtot = $res ? $DB->numrows($res) : 0; if ($limit) { $start = isset($_GET["start"]) ? $_GET["start"] : 0; if ($start >= $nbtot) { $start = 0; } if ($start > 0 || $start + $limit < $nbtot) { $res = $DB->query($query . " LIMIT {$start},{$limit}"); } } else { $start = 0; } if ($nbtot == 0) { if (!$HEADER_LOADED) {