File "Position.php"
Full Path: /home/humancap/cl.humancap.com.my/admin/class/crud/Position.php
File size: 17.44 KB
MIME-type: text/x-php
Charset: utf-8
<?php
namespace crud;
use common\Utils;
use phpformbuilder\database\DB;
use phpformbuilder\database\Pagination;
use secure\Secure;
class Position extends Elements
{
// item name passed in url
public $item;
// item name displayed
public $item_label;
// associative array : field => field displayed name
public $fields;
// external relations
public $external_tables_count = 1;
public $external_fields_count;
public $external_rows_count;
public $external_tables_labels = array('Competency');
public $external_add_btn = array();
public $external_fields = array();
// primary key passed to create|edit|delete
public $primary_keys; // primary keys fieldnames
// CREATE rights
public $can_create = false;
public $pks = array(); // primary key values for each row
public $pk_concat_values = array(); // concatenated values of primary key(s) for each row
public $pk_url_params = array(); // primary key(s) sent to the edit/delete forms URL for each row
public $update_record_authorized = array();
public $id = array();
public $name = array();
public $nama = array();
public $industry_id = array();
public $active_filtered_fields = array();
public $debug_content = '';
public $export_data_button;
public $filters_form;
public $is_single_view = false;
public $item_url;
public $join_query = '';
public $main_pdo_settings = array();
public $pagination_html;
// Array of primary fieldnames => values to select a single record for view
public $params;
public $records_count;
public $select_number_per_page;
public $sorting;
public function __construct($element, $params = array())
{
$this->table = $element->table;
$this->item = $element->item;
$this->item_label = $element->item_label;
$this->primary_keys = $element->primary_keys;
$this->select_data = $element->select_data;
$this->fields = $element->fields;
$table = $this->table;
$this->params = $params;
if (!empty($params)) {
$this->is_single_view = true;
}
$json = file_get_contents(ADMIN_DIR . 'crud-data/' . $this->item . '-filter-data.json');
$filters_array = json_decode($json, true);
$this->item_url = $_SERVER['REQUEST_URI'];
// connect to the database
$db = new Pagination(DEBUG);
$db->setDebugMode('register');
$this->join_query = ' LEFT JOIN industry ON position.industry_id=industry.id';
$columns = 'position.id, position.name, position.nama, industry.id AS ind_id, industry.name AS ind_nam';
$where = array();
// restricted rights query
if (Secure::canReadRestricted($table)) {
$where = array_merge($where, Secure::getRestrictionQuery($table));
}
// filters
$filters = new ElementsFilters($table, $filters_array, $this->join_query);
$this->active_filtered_fields = $filters->getActiveFilteredFields();
$where_filters = $filters->getWhere();
$where = array_merge($where, $where_filters);
// search
$where_search = array();
if (isset($_POST['search_field']) && isset($_POST['search_string'])) {
$searchVals = explode(' + ', $_POST['search_string']);
$search_string = $searchVals[0];
$_SESSION['rp_search_field'][$table] = $_POST['search_field'];
$_SESSION['rp_search_string'][$table] = $search_string;
if (sizeof($searchVals) > 1) {
$_SESSION['rp_search_string_2'][$table] = $searchVals[1];
} else {
unset($_SESSION['rp_search_string_2'][$table]);
}
}
if (isset($_SESSION['rp_search_string'][$table]) && !empty($_SESSION['rp_search_string'][$table])) {
$sf = $_SESSION['rp_search_field'][$table];
$search_field = $table . '.' . $sf;
$search_field2 = '';
$search_string_sqlvalue = $db->safe('%' . $_SESSION['rp_search_string'][$table] . '%');
if (isset($_SESSION['rp_search_string_2'][$table])) {
$search_string_2_sqlvalue = $db->safe('%' . $_SESSION['rp_search_string_2'][$table] . '%');
}
if (file_exists(ADMIN_DIR . 'crud-data/' . $this->item . '-select-data.json')) {
$json = file_get_contents(ADMIN_DIR . 'crud-data/' . $this->item . '-select-data.json');
$selects_array = json_decode($json, true);
if (isset($selects_array[$sf]) && $selects_array[$sf]['from'] == 'from_table') {
$search_field = $selects_array[$sf]['from_table'] . '.' . $selects_array[$sf]['from_field_1'];
if (!empty($selects_array[$sf]['from_field_2'])) {
$search_field2 = $selects_array[$sf]['from_table'] . '.' . $selects_array[$sf]['from_field_2'];
}
}
}
$where_search[] = 'LOWER(' . $search_field . ') LIKE LOWER(' . $search_string_sqlvalue . ')';
if (!empty($search_field2) && isset($search_string_2_sqlvalue) && ($search_string_2_sqlvalue != "'%%'")) {
$where_search[] = 'LOWER(' . $search_field2 . ') LIKE LOWER(' . $search_string_2_sqlvalue . ')';
}
$where = array_merge($where, $where_search);
}
$this->filters_form = $filters->returnForm($this->item_url);
// Get join queries from active filters
$active_filters_join_queries = $filters->buildElementJoinQuery();
if (isset($_POST['search_field'])) {
$pagination_url = str_replace(ADMIN_URL . 'search/', ADMIN_URL, $_SERVER['REQUEST_URI']);
} else {
$pagination_url = $_SERVER['REQUEST_URI'];
}
if (isset($_POST['npp']) && is_numeric($_POST['npp'])) {
$_SESSION['npp'] = $_POST['npp'];
} elseif (!isset($_SESSION['npp'])) {
$_SESSION['npp'] = 20;
}
if ($this->is_single_view) {
// if single record view
$active_filters_join_queries = $filters->buildElementJoinQuery();
$pagination_url = '';
// replace 'fieldname' with 'table.fieldname' to avoid ambigous query
$where_params = array_combine(
array_map(function ($k) {
return $this->table . '.' . $k;
}, array_keys($this->params)),
$this->params
);
$where = array_merge($where, $where_params);
}
// order query
$this->sorting = ElementsUtilities::getSorting($table, 'id', 'ASC');
$npp = $_SESSION['npp'];
if (!empty($where_search) && PAGINE_SEARCH_RESULTS === false) {
$npp = 1000000;
}
if (empty($where)) {
$where = null;
}
// $this->main_pdo_settings are the PDO settings without the pagination LIMIT.
$this->main_pdo_settings = array(
'function' => 'select',
'from' => 'position' . $active_filters_join_queries,
'values' => $columns,
'where' => $where,
'extras' => array('order_by' => $this->sorting),
'debug' => DEBUG_DB_QUERIES
);
$this->pagination_html = $db->pagine($this->main_pdo_settings, $npp, 'p', $pagination_url, 5, true, '/', '');
if (DEBUG_DB_QUERIES) {
$this->debug_content .= '<p class="debug-title text-bg-info">"' . $this->table . '" queries</p>' . $db->getDebugContent();
}
$update_authorized = false;
if (Secure::canUpdate($this->table)) {
// user can update ALL the records
$update_authorized = true;
}
$this->records_count = $db->rowCount();
if (!empty($this->records_count)) {
while ($row = $db->fetch()) {
$primary_keys_array = array(
'id' => $row->id
);
$this->pks[] = $primary_keys_array;
$pk_concatenated_values = $row->id;
$this->pk_concat_values[] = $pk_concatenated_values;
$this->update_record_authorized[$pk_concatenated_values] = $update_authorized;
$this->pk_url_params[] = http_build_query($primary_keys_array, '', '/');
$this->id[] = $row->id;
$this->name[] = $row->name;
$this->nama[] = $row->nama;
$this->industry_id[] = $row->ind_nam;
}
}
// Autocomplete doesn't need the followings settings
if (!isset($_POST['is_autocomplete'])) {
if (!$this->is_single_view) {
// CREATE/DELETE rights
if (Secure::canCreate($table) || Secure::canCreateRestricted($table)) {
$this->can_create = true;
}
// restricted UPDATE rights
if (Secure::canUpdateRestricted($table)) {
$where = array_merge(
Secure::getRestrictionQuery($table),
$where_filters,
$where_search
);
$pdo_settings = array(
'function' => 'select',
'from' => 'position' . $active_filters_join_queries,
'values' => $columns,
'where' => $where,
'extras' => array('order_by' => $this->sorting),
'debug' => DEBUG_DB_QUERIES
);
// get authorized update primary keys
$db->pagine($pdo_settings, $npp, 'p', $pagination_url, 5, true, '/', '');
if (DEBUG_DB_QUERIES) {
$this->debug_content .= '<p class="debug-title text-bg-info">"' . $this->table . '" - get authorized update primary keys</p>' . $db->getDebugContent();
}
$records_count = $db->rowCount();
if (!empty($records_count)) {
while ($row = $db->fetch()) {
$this->update_record_authorized[$row->id] = true;
}
}
}
}
/* external relations */
for ($i=0; $i < count($this->pks); $i++) {
$this->external_rows_count[$i] = array();
$this->external_fields[$i] = array();
$this->external_add_btn[$i] = array();
// position => position_competency => competency
$from = 'position INNER JOIN position_competency ON position_competency.position_id=position.id INNER JOIN competency ON position_competency.competency_id=competency.id';
$values = 'position_competency.id AS position_competency_id, competency.name, competency.nama, competency.id AS target_table_pk_0';
$where = array();
foreach ($this->pks[$i] as $key => $value) {
$where[] = 'position.' . $key . ' = ' . $value;
}
$db->select($from, $values, $where, array('order_by' => $this->sorting), DEBUG_DB_QUERIES);
if (DEBUG_DB_QUERIES) {
if ($i === 0) {
$this->debug_content .= '<p class="debug-title text-bg-info">"competency" queries <small>(External relation)</small></p>' . $db->getDebugContent();
} else {
$this->debug_content .= $db->getDebugContent();
}
}
$records_count = $db->rowCount();
$this->external_rows_count[$i][] = $records_count;
$ext_fields = array(
'table' => 'competency',
'table_label' => 'Competency',
'uniqid' => 'f-' . uniqid(),
'fields' => array(
'name' => array(),
'nama' => array()
),
'fieldnames' => array(
'name' => 'name',
'nama' => 'nama'
)
);
// get user custom fieldnames
$ext_fieldnames = ElementsUtilities::getFieldNames($ext_fields['table']);
if ($ext_fieldnames !== false) {
foreach ($ext_fields['fieldnames'] as $key => $value) {
if (isset($ext_fieldnames[$key])) {
$ext_fields['fieldnames'][$key] = $ext_fieldnames[$key];
}
}
}
if (!$this->is_single_view) {
// add button
$add_btn = '';
if (Secure::canCreate('competency')) {
if (!empty($records_count)) {
// add button for nested table
$add_btn = '<div class="d-flex flex-row-reverse mb-2">';
$add_btn .= ' <a href="' . ADMIN_URL . 'competency/create?id=' . $this->pks[$i]['id'] . '" class="btn btn-xs btn-primary" data-bs-title="Add new" data-bs-toggle="tooltip"><span class="fas fa-plus-circle prepend"></span>Add new Competency</a>';
$add_btn .= '</div>';
} else {
// add button for empty cell
$add_btn = '<div class="d-flex justify-content-center">';
$add_btn .= ' <a href="' . ADMIN_URL . 'competency/create?id=' . $this->pks[$i]['id'] . '" class="btn btn-xs btn-outline-secondary" data-bs-title="Add new" data-bs-toggle="tooltip"><span class="fas fa-plus-circle prepend"></span>Add new</a>';
$add_btn .= '</div>';
}
}
$this->external_add_btn[$i][] = $add_btn;
}
if (!empty($records_count)) {
while ($row = $db->fetch()) {
$json = false;
if (!is_null($row->name)) {
$test_if_json = json_decode($row->name);
if (json_last_error() == JSON_ERROR_NONE && is_array($test_if_json)) {
$json = $test_if_json;
}
}
if ($json) {
$ext_fields['fields']['name'][] = implode(', ', $json);
} else {
$ext_fields['fields']['name'][] = $row->name;
}
$json = false;
if (!is_null($row->nama)) {
$test_if_json = json_decode($row->nama);
if (json_last_error() == JSON_ERROR_NONE && is_array($test_if_json)) {
$json = $test_if_json;
}
}
if ($json) {
$ext_fields['fields']['nama'][] = implode(', ', $json);
} else {
$ext_fields['fields']['nama'][] = $row->nama;
}
if (!$this->is_single_view) {
// edit/delete buttons
if (Secure::canUpdate('competency') || Secure::canCreate('competency')) {
$action_btns = '<div class="btn-group">';
$relation_table_pk_columns = array(
'id' => $row->target_table_pk_0
);
$url_params = http_build_query($relation_table_pk_columns, '', '/');
if (Secure::canUpdate('competency')) {
$action_btns .= '<a href="' . ADMIN_URL . 'competency/edit/' . $url_params . '" class="btn btn-xs btn-warning" data-bs-title="' . addslashes(EDIT) . '" rel="noindex" data-bs-toggle="tooltip"><span class="fas fa-pencil-alt"></span></a>';
}
if (Secure::canCreate('competency')) {
$action_btns .= '<a href="' . ADMIN_URL . 'competency/delete/' . $url_params . '" class="btn btn-xs btn-danger" data-bs-title="' . addslashes(DELETE_CONST) . '" rel="noindex" data-bs-toggle="tooltip"><span class="fas fa-times-circle"></span></a>';
}
$action_btns .= '</div>';
$ext_fields['fieldnames']['action'] = ACTION_CONST;
$ext_fields['fields']['action'][] = $action_btns;
} // end if
} // end if !$this->is_single_view
} // end while
} // end if
$this->external_fields[$i][] = $ext_fields;
} // end for
$this->external_fields_count = count($this->external_fields);
} // end if
if (!$this->is_single_view) {
// Export data button
$this->export_data_button = ElementsUtilities::exportDataButtons($table, $this->main_pdo_settings);
// number/page
$numbers_array = array(5, 10, 20, 50, 100, 200, 10000);
$this->select_number_per_page = ElementsUtilities::selectNumberPerPage($numbers_array, $_SESSION['npp'], $this->item_url);
}
}
}