', UI::h('small', [], "Zmiany w polu 'Osoba odpowiedzialna' we wszystkich rekordach wybranej tabeli."), ]); } function defaultView() { $this->titleView(); $SELECTED_TABLE_ID = V::get('id_table', '', $_GET); if (!$SELECTED_TABLE_ID) { $fromNs = V::get('_fromNamespace', '', $_GET); if (!empty($fromNs)) { try { $acl = ACL::getAclByNamespace($fromNs); $SELECTED_TABLE_ID = $acl->getID(); } catch (Exception $e) { DBG::log($e); } } } $SELECTED_FROM_OWNER_ID = V::get('id_from', '', $_GET); $SELECTED_TO_OWNER_ID = V::get('id_to', '', $_GET); $SELECTED_TABLE_INFO = ($SELECTED_TABLE_ID) ? $this->getTableInfo($SELECTED_TABLE_ID) : []; $SELECTED_FROM_OWNER_INFO = ($SELECTED_FROM_OWNER_ID) ? $this->getUserInfo($SELECTED_FROM_OWNER_ID) : []; $SELECTED_TO_OWNER_INFO = ($SELECTED_TO_OWNER_ID) ? $this->getUserInfo($SELECTED_TO_OWNER_ID) : []; DBG::log($SELECTED_TABLE_INFO, 'array', "SELECTED_TABLE_INFO ({$SELECTED_TABLE_ID})"); DBG::log($SELECTED_FROM_OWNER_INFO, 'array', "SELECTED_FROM_OWNER_INFO ({$SELECTED_FROM_OWNER_ID})"); DBG::log($SELECTED_TO_OWNER_INFO, 'array', "SELECTED_TO_OWNER_INFO ({$SELECTED_TO_OWNER_ID})"); echo UI::h('div', [ 'id' => "p5-change-owner-widget" ]); echo UI::h('style', [], " .dropdown-menu>li>a { max-width: 800px; } "); echo UI::h('script', [ 'src' => "static/vendor.js" ]); // window.p5VendorJs: {React, ReactDOM, createReactClass, Redux} UI::inlineJS(__FILE__ . '.view.js', [ 'HTML_ID' => "p5-change-owner-widget", 'URL_SEARCH_TABLE_LIST' => $this->getLink('searchTableListAjaxTask'), 'URL_SEARCH_USER_LIST' => $this->getLink('searchUserListAjaxTask'), 'URL_FETCH_INFO' => $this->getLink('fetchInfoAjaxTask'), 'SELECTED_TABLE_ID' => $SELECTED_TABLE_ID, 'SELECTED_TABLE_INFO' => $SELECTED_TABLE_INFO, 'SELECTED_FROM_OWNER_ID' => $SELECTED_FROM_OWNER_ID, 'SELECTED_FROM_OWNER_INFO' => $SELECTED_FROM_OWNER_INFO, 'SELECTED_TO_OWNER_ID' => $SELECTED_TO_OWNER_ID, 'SELECTED_TO_OWNER_INFO' => $SELECTED_TO_OWNER_INFO, 'CHANGE_OWNER_POST_TASK_NAME' => 'changeOwnerPostTask', 'URL_BASE' => $this->getLink(), ]); } function changeOwnerView() { $this->titleView(); $id_table = V::get('id_table', '', $_POST); $id_from = V::get('id_from', '', $_POST); $id_to = V::get('id_to', '', $_POST); if (!$id_table) throw new Exception("Missing id_table"); if (!$id_from) throw new Exception("Missing id_from"); if (!$id_to) throw new Exception("Missing id_to"); $stats = $this->fetchInfoAjaxTask([ 'id_table' => $id_table, 'id_from' => $id_from, 'id_to' => $id_to, ]); DBG::log($stats, 'array', '$stats'); $tableInfo = $this->getTableInfo($id_table); DBG::log($tableInfo, 'array', '$tableInfo'); $tableLabel = (!empty($tableInfo)) ? $tableInfo[0]['label'] : ""; echo UI::h('div', [ 'class' => "alert alert-info" ], "Wprowadzanie zmian w tabeli [{$id_table}] {$tableLabel}"); $totalToUpdate = $stats['body']['fromInfo']['total']; if (!$totalToUpdate) throw new Exception("Brak rekordów do aktualizacji"); $namespace = DB::getPDO()->fetchValue(" select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = '{$id_table}' limit 1 "); $acl = ACL::getAclByNamespace($namespace); $rootTableName = $acl->getRootTableName(); $primaryKeyField = $acl->getPrimaryKeyField(); $sqlTable = DB::getPDO()->identifierQuote($rootTableName); { // TODO: loop / sql limit try { $rowsToUpdate = DB::getPDO()->fetchAll(" select t.ID, t.L_APPOITMENT_USER from {$sqlTable} t where t.L_APPOITMENT_USER = :id_from limit 100 ", [ ':id_from' => $id_from, ]); UI::table(['rows'=>$rowsToUpdate]); } catch (Exception $e) { // msg: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.L_APPOITMENT_USER' in 'field list'" DBG::log($e); if ($e->getCode() === '42S22') { throw new Exception("Brak pola Osoba odpowiedzialna w tabeli '{$namespace}'"); } throw $e; } foreach ($rowsToUpdate as $item) { try { DB::getPDO()->update($rootTableName, $primaryKeyField, $item['ID'], [ 'L_APPOITMENT_USER' => $id_to, 'A_RECORD_UPDATE_DATE' => "NOW()", 'A_RECORD_UPDATE_AUTHOR' => User::getLogin(), ]); DB::getPDO()->insert($rootTableName . "_HIST", [ 'ID_USERS2' => $item['ID'], 'L_APPOITMENT_USER' => $id_to, 'A_RECORD_UPDATE_DATE' => "NOW()", 'A_RECORD_UPDATE_AUTHOR' => User::getLogin(), ]); } catch (Exception $e) { DBG::log($e); UI::alert('danger', [], "Nie udało się zaktualizować rekordu '{$item['ID']}': " . $e->getMessage()); } } } UI::alert('success', "Koniec"); } function getTableInfo($idTable) { $userObject = SchemaFactory::loadDefaultObject('UserObject'); $userObject->setIdUser(User::getID()); // $userObject->setIdProcesFilter($filterIdProces); $items = $userObject->getItems([ 'cols' => [ 'ID_TABLE', 'name', 'label' ], // , 'opis' ], 'primaryKey' => $idTable, ]); // UI::table([ 'rows' => $items ]); $items = array_map(function ($item) { // [16] => Array: // [ID_TABLE] => 16 // [ID_USER] => 4517 // [ID_PROCES] => // [db] => 36 // [name] => BUILDINGS // [label] => Budynki // [opis] => Budynki - tabela budynków w zasięgu lub zainteresowanych podłączeniem do sieci BIALL-NET. // [_rootTableName] => BUILDINGS // [namespace] => default_db/BUILDINGS return [ 'id' => $item['ID_TABLE'], 'label' => $this->getTableLabelFromRow($item), '_item_name' => $item['name'], '_item_label' => $item['label'], '_item_name_substr' => substr($item['name'], 0, strlen($item['label'])), '_item' => $item, ]; }, $items); return (!empty($items)) ? [ reset($items) ] : []; } function getUserInfo($userLogin) { return DB::getPDO()->fetchAll(" select u.ADM_ACCOUNT as id , u.ADM_NAME as label from ADMIN_USERS u where u.ADM_ACCOUNT = :login ", [ ':login' => $userLogin ]); } function searchTableListAjaxTaskAction() { Response::sendTryCatchJson([$this, 'searchTableListAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); } function searchTableListAjaxTask($args) { $query = V::get('query', '', $args); $wordsQuery = $this->convertQueryToSearchWords($query); $items = $this->fetchAllowedTableList($wordsQuery); return [ 'type' => 'success', 'msg' => 'OK', '__DBG__args' => $args, 'body' => [ 'items' => array_values($items), ] ]; } function searchUserListAjaxTaskAction() { Response::sendTryCatchJson([$this, 'searchUserListAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); } function searchUserListAjaxTask($args) { $query = V::get('query', '', $args); $wordsQuery = $this->convertQueryToSearchWords($query); $sqlListWhere = []; $pdo = DB::getPDO(); foreach ($wordsQuery as $word) { $sqlListWhere[] = implode(" or ", [ "u.ID like " . $pdo->quote("%{$word}%"), "u.ADM_ACCOUNT like " . $pdo->quote("%{$word}%"), "u.ADM_NAME like " . $pdo->quote("%{$word}%"), ]); } $sqlWhere = (!empty($sqlListWhere)) ? implode(" and ", $sqlListWhere) : "1=1"; $items = DB::getPDO()->fetchAll(" select u.ADM_ACCOUNT as id , u.ADM_NAME as label from ADMIN_USERS u where {$sqlWhere} ", [ ':id' => $idUser ]); return [ 'type' => 'success', 'msg' => 'OK', 'body' => [ 'items' => array_values($items), ] ]; } function convertQueryToSearchWords($query) { $query = trim($query); if (strlen($query) < 3) throw new Exception("Query must be more then 2 chars"); $wordsQuery = []; if (false !== strpos($query, ' ')) { $words = explode(' ', $query); DBG::log($words, 'array', "\$words from \$query({$query}) - 1"); $words = array_map(function ($word) { return trim(trim($word), '0'); }, $words); DBG::log($words, 'array', "\$words from \$query({$query}) - 2"); $words = array_filter($words, function ($word) { return (strlen($word) > 2); }); DBG::log($words, 'array', "\$words from \$query({$query}) - 3"); if (empty($words)) throw new Exception("Query words must be more then 2 chars"); $wordsQuery = $words; } else { $wordsQuery[] = $query; } return $wordsQuery; } function fetchAllowedTableList($wordsQuery) { $userObject = SchemaFactory::loadDefaultObject('UserObject'); $userObject->setIdUser(User::getID()); // $userObject->setIdProcesFilter($filterIdProces); $items = $userObject->getItems([ 'cols' => [ 'ID_TABLE', 'name', 'label' ], // , 'opis' ], ]); // UI::table([ 'rows' => $items ]); $items = array_filter($items, function ($item) use ($wordsQuery) { foreach ($wordsQuery as $word) { // DBG::log([ // 'is1' => ($item['ID_TABLE'] == $word), // 'is2' => (false !== stripos($item['name'], $word)), // 'is3' => (false !== stripos($item['label'], $word)), // '$item' => $item, // ], 'array', "DBG:array_filter({$item['ID_TABLE']}) '{$word}'"); if ($item['ID_TABLE'] == $word) return true; if (false !== stripos($item['name'], $word)) return true; if (false !== stripos($item['label'], $word)) return true; } return false; }); // UI::table([ 'rows' => $items ]); return array_map(function ($item) { // [16] => Array: // [ID_TABLE] => 16 // [ID_USER] => 4517 // [ID_PROCES] => // [db] => 36 // [name] => BUILDINGS // [label] => Budynki // [opis] => Budynki - tabela budynków w zasięgu lub zainteresowanych podłączeniem do sieci BIALL-NET. // [_rootTableName] => BUILDINGS // [namespace] => default_db/BUILDINGS return [ 'id' => $item['ID_TABLE'], '_item_name' => $item['name'], '_item_label' => $item['label'], '_item_name_substr' => substr($item['name'], 0, strlen($item['label'])), 'label' => $this->getTableLabelFromRow($item), '_item' => $item, ]; }, $items); } function getTableLabelFromRow($item) { $label = $item['name']; if (!empty($item['label'])) { $tableName = (false !== strpos($item['name'], '/')) ? substr($item['name'], strrpos($item['name'], '/') + 1) : $item['name']; if (strlen($item['label']) < strlen($tableName) && substr($tableName, 0, strlen($item['label'])) === $item['label']) { $label = $tableName; } else { $label = $item['label']; } } // if (!empty($item['opis']) && !empty($item['label'])) { // } return $label; } function fetchInfoAjaxTaskAction() { Response::sendTryCatchJson([$this, 'fetchInfoAjaxTask'], $args = 'JSON_FROM_REQUEST_BODY'); } function fetchInfoAjaxTask($args) { $id_table = V::get('id_table', '', $args); $id_from = V::get('id_from', '', $args); $id_to = V::get('id_to', '', $args); if (!$id_table) throw new Exception("Missing id table"); $responseBody = []; $responseBody['by_owner'] = []; // [ { login, total }, ... ] $responseBody['fromInfo'] = []; // { id, name, status, total, ... } $responseBody['toInfo'] = []; // { id, name, status, total, ... } $responseBody['errors'] = []; // [ { type, msg } ] $responseBody['error_missing_owner_field'] = false; // bool $namespace = DB::getPDO()->fetchValue(" select o.namespace from `CRM_#CACHE_ACL_OBJECT` o where o.idZasob = '{$id_table}' limit 1 "); $acl = ACL::getAclByNamespace($namespace); $rootTableName = $acl->getRootTableName(); $sqlTable = DB::getPDO()->identifierQuote($rootTableName); try { $responseBody['by_owner'] = DB::getPDO()->fetchAll(" select t.L_APPOITMENT_USER as id, count(*) as total from {$sqlTable} t where t.L_APPOITMENT_USER = :id_from or t.L_APPOITMENT_USER = :id_to group by t.L_APPOITMENT_USER limit 10 ", [ ':id_from' => ($id_from) ? $id_from : "XXXXX", ':id_to' => ($id_to) ? $id_to : "XXXXX", ]); } catch (Exception $e) { // msg: "SQLSTATE[42S22]: Column not found: 1054 Unknown column 't.L_APPOITMENT_USER' in 'field list'" DBG::log($e); if ($e->getCode() === '42S22') { $responseBody['error_missing_owner_field'] = true; } $responseBody['errors']['by_owner'] = $e->getMessage(); } $fromInfo = $this->_fetchOwnerInfo($id_from); $toInfo = $this->_fetchOwnerInfo($id_to); $responseBody['fromInfo'] = (!$fromInfo) ? null : array_merge($fromInfo, [ 'total' => array_reduce($responseBody['by_owner'], function ($ret, $item) use ($id_from) { return ($item['id'] === $id_from) ? $item['total'] : $ret; }, 0) ]); $responseBody['toInfo'] = (!$toInfo) ? null : array_merge($toInfo, [ 'total' => array_reduce($responseBody['by_owner'], function ($ret, $item) use ($id_to) { return ($item['id'] === $id_to) ? $item['total'] : $ret; }, 0) ]); return [ 'type' => 'success', 'msg' => 'OK', '__DBG__namespace' => $namespace, '__DBG__acl' => $acl, '__DBG__args' => $args, 'body' => $responseBody, ]; } function _fetchOwnerInfo($id) { // @param $id: string || null return DB::getPDO()->fetchFirst(" select t.ADM_ACCOUNT as id , t.ADM_NAME as name , t.EMPLOYEE_TYPE as type , t.A_STATUS as status from `ADMIN_USERS` t where t.ADM_ACCOUNT = :id_from ", [ ':id_from' => $id, ]); } }