$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) {
