top.window.location = '../../index.php'";
exit;
}
require_once __DIR__ . "/" . "../../includes/include_basics_only.php";
require_once __DIR__ . "/" . "../../includes/classes/ConnectPDO.php";
use includes\classes\ConnectPDO;
$conn = ConnectPDO::getInstance();
$exception = "";
$criteria = [];
$criteriaText = "";
$data = [];
$data['success'] = true;
$data['message'] = "";
$data['total'] = 0;
$post = $_POST;
$client = (isset($post['client']) ? noHtml($post['client']) : "");
$asset_units = (isset($post['asset_unit']) && !empty(array_filter($post['asset_unit'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['asset_unit']) : []);
$asset_departments = (isset($post['asset_department']) && !empty(array_filter($post['asset_department'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['asset_department']) : []);
$asset_users = (isset($post['asset_user']) && !empty(array_filter($post['asset_user'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['asset_user']) : []);
$asset_categories = (isset($post['asset_category']) && !empty(array_filter($post['asset_category'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['asset_category']) : []);
$asset_types = (isset($post['asset_type']) && !empty(array_filter($post['asset_type'], function($v) { return !empty($v); })) ? array_map('noHtml', $post['asset_type']) : []);
$use_asset_client = (isset($post['use_asset_client']) && !empty($post['use_asset_client']) ? true : false);
$use_asset_department = (isset($post['use_asset_department']) && !empty($post['use_asset_department']) ? true : false);
$termsTotal = "";
$terms = "";
$chart_title_sufix = '_chart_title';
$chart_1_prefix = 'chart_01';
$chart_2_prefix = 'chart_02';
$chart_3_prefix = 'chart_03';
$chart_4_prefix = 'chart_04';
$chart_5_prefix = 'chart_05';
/* Critérios da consulta */
$criteria['client'] = TRANS('CLIENT') . ': ' . TRANS('ALL');
$criteria['units'] = TRANS('COL_UNIT') . ': ' . TRANS('ALL');
$criteria['departments'] = TRANS('DEPARTMENT') . ': ' . TRANS('ALL');
$criteria['users'] = TRANS('FIELD_USER') . ': ' . TRANS('ALL');
$criteria['categories'] = TRANS('CATEGORY') . ': ' . TRANS('ALL');
$criteria['asset_type'] = TRANS('COL_TYPE') . ': ' . TRANS('ALL');
$units_text_ids = "";
$departments_text_ids = "";
$users_text_ids = "";
$categories_text_ids = "";
$asset_types_text_ids = "";
$client_filter = [];
$units_filter = [];
$types_filter = [];
$sql_terms = "";
/* Critérios de Cliente e unidades */
if (!empty($client)) {
/* Cliente do usuário - padrão */
// $sql_terms .= " AND clu.id = '{$client}' ";
$sql_terms .= ($use_asset_client ? " AND cla.id = '{$client}'" : " AND clu.id = '{$client}' ");
$client_filter = getClients($conn, $client);
$criteria['client'] = TRANS('CLIENT') . ': ' . $client_filter['nickname'] . '';
if (empty($asset_units)) {
$units_filter = getUnits($conn, null, null, $client);
$units_filter = (!empty($units_filter) ? $units_filter : [['inst_cod' => 0]]);
} else {
foreach ($asset_units as $unit) {
$units_filter[] = getUnits($conn, null, $unit);
}
$criteria['units'] = TRANS('COL_UNIT') . ': ' . implode(', ', array_column($units_filter, 'inst_nome')) . '';
$units_text_ids = implode(', ', array_column($units_filter, 'inst_cod'));
$sql_terms .= " AND c.comp_inst IN ({$units_text_ids}) ";
}
} elseif (!empty($asset_units)) {
foreach ($asset_units as $unit) {
$units_filter[] = getUnits($conn, null, $unit);
}
$criteria['units'] = TRANS('COL_UNIT') . ': ' . implode(', ', array_column($units_filter, 'inst_nome')) . '';
$units_text_ids = implode(', ', array_column($units_filter, 'inst_cod'));
$sql_terms .= " AND c.comp_inst IN ({$units_text_ids}) ";
}
/* Critérios de Departamentos */
if (!empty($asset_departments)) {
foreach ($asset_departments as $department) {
$departments_filter[] = getDepartments($conn, null, $department);
}
$criteria['departments'] = TRANS('DEPARTMENT') . ': ' . implode(', ', array_column($departments_filter, 'local')) . '';
$departments_text_ids = implode(', ', array_column($departments_filter, 'loc_id'));
/* Departamento do usuário (padrao) */
// $sql_terms .= " AND u.user_department IN ({$departments_text_ids}) ";
$sql_terms .= ($use_asset_department ? " AND c.comp_local IN ({$departments_text_ids})" :" AND u.user_department IN ({$departments_text_ids}) ");
}
/* Critérios de Usuários */
if (!empty($asset_users)) {
foreach ($asset_users as $user) {
$users_filter[] = getUsers($conn, $user);
}
$criteria['users'] = TRANS('FIELD_USER') . ': ' . implode(', ', array_column($users_filter, 'nome')) . '';
$users_text_ids = implode(', ', array_column($users_filter, 'user_id'));
$sql_terms .= " AND u.user_id IN ({$users_text_ids}) ";
}
/* Critério de Tipo de ativos com base no filtro de categoria */
if (!empty($asset_categories)) {
foreach ($asset_categories as $categorie) {
$categories_filter[] = getAssetsCategories($conn, $categorie);
}
$criteria['categories'] = TRANS('CATEGORY') . ': ' . implode(', ', array_column($categories_filter, 'cat_name')) . '';
$categories_text_ids = implode(', ', array_column($categories_filter, 'id'));
$sql_terms .= " AND cat.id IN ({$categories_text_ids}) ";
if (empty($asset_types)) {
$types_filter = getAssetsTypes($conn, null, null, $asset_categories );
} else {
foreach ($asset_types as $asset_type) {
$types_filter[] = getAssetsTypes($conn, $asset_type);
}
$criteria['asset_type'] = TRANS('COL_TYPE') . ': ' . implode(', ', array_column($types_filter, 'tipo_nome')) . '';
$asset_types_text_ids = implode(', ', array_column($types_filter, 'tipo_cod'));
$sql_terms .= " AND c.comp_tipo_equip IN ({$asset_types_text_ids}) ";
}
} elseif (!empty($asset_types)) {
foreach ($asset_types as $asset_type) {
$types_filter[] = getAssetsTypes($conn, $asset_type);
}
$criteria['asset_type'] = TRANS('COL_TYPE') . ': ' . implode(', ', array_column($types_filter, 'tipo_nome')) . '';
$asset_types_text_ids = implode(', ', array_column($types_filter, 'tipo_cod'));
$sql_terms .= " AND c.comp_tipo_equip IN ({$asset_types_text_ids}) ";
}
$criteriaText = implode(' | ', $criteria);
if (!empty($_SESSION['s_allowed_units'])) {
$criteriaText .= "
(". TRANS('RESULT_LIMITED_BY_PERMISSIONS').")";
}
$data['criteria'] = $criteriaText;
// var_dump($criteriaText);
/* Controle no limite de visualização para a área primária do usuário */
// if (!empty($_SESSION['s_allowed_units'])) {
// $terms .= " AND e.comp_inst IN ({$_SESSION['s_allowed_units']}) ";
// $termsTotal .= (!empty($termsTotal) ? " AND " : " WHERE ");
// $termsTotal .= " e.comp_inst IN ({$_SESSION['s_allowed_units']}) ";
// }
/* Tabela de custos por usuário com base nos critérios informados */
$sql = "SELECT
SUM(CAST(c.comp_valor AS DECIMAL(10,2))) AS amount,
u.nome,
clu.nickname AS cliente_usuario,
COALESCE (lu.local, 'N/A') AS departamento_usuario
FROM
users_x_assets uxa,
clients clu,
usuarios u,
equipamentos c,
instituicao i
LEFT JOIN clients AS cla ON cla.id = i.inst_client,
localizacao la,
usuarios ud
LEFT JOIN localizacao AS lu ON lu.loc_id = ud.user_department,
tipo_equip t
LEFT JOIN assets_categories cat ON cat.id = t.tipo_categoria
WHERE
uxa.user_id = u.user_id AND
uxa.user_id = ud.user_id AND
uxa.is_current = 1 AND
uxa.asset_id = c.comp_cod AND
clu.id = u.user_client AND
c.comp_inst = i.inst_cod AND
c.comp_tipo_equip = t.tipo_cod AND
c.comp_local = la.loc_id
{$sql_terms}
GROUP BY
u.user_id,
u.nome,
cliente_usuario,
departamento_usuario
HAVING
amount > 0
ORDER BY
amount DESC";
try {
$res = $conn->prepare($sql);
$res->execute();
$result = $res->fetchAll();
$data['table'] = $result;
/* somatório do campo 'amount' */
$data['total'] = priceScreen(array_sum(array_column($result, 'amount')));
/* Aplica a função 'priceScreen' em cada elemento da coluna 'amount' - function (&$value, $key) */
array_walk($data['table'], function (&$value) {
/* armazena o valor original da nova coluna 'price_db' */
$value['price_db'] = $value['amount'];
$value['amount'] = priceScreen($value['amount']);
});
$data['table']['title'] = TRANS('ASSETS_COST_BY_USER');
} catch (Exception $e) {
$exception .= "