123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554 |
- <?php
- /**
- * Bibliotheca
- *
- * Copyright 2018-2023 Johannes Keßler
- *
- * This program is free software: you can redistribute it and/or modify
- * it under the terms of the GNU General Public License as published by
- * the Free Software Foundation, either version 3 of the License, or
- * (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful,
- * but WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
- * GNU General Public License for more details.
- *
- * You should have received a copy of the GNU General Public License
- * along with this program. If not, see http://www.gnu.org/licenses/gpl-3.0.
- */
- /**
- * Class Mancubus everything to show an entry
- */
- class Mancubus {
- /**
- * The database object
- *
- * @var mysqli
- */
- private mysqli $_DB;
- /**
- * The user object to query with
- *
- * @var Doomguy
- */
- private Doomguy $_User;
- /**
- * Currently loaded collection to work with
- *
- * @var string Number
- */
- private string $_collectionId;
- /**
- * Options for db queries
- * 'limit' => int,
- * 'offset' => int,
- * 'orderby' => string,
- * 'sortDirection' => ASC|DESC
- *
- * @var array
- */
- private array $_queryOptions;
- /**
- * Store the all the values for an entry from lookup table
- *
- * @var array
- */
- private array $_cacheLookupValuesForEntry = array();
- /**
- * Store entryFields for run time
- *
- * @var array
- */
- private array $_cacheEntryFields = array();
- /**
- * Mancubus constructor.
- *
- * @param mysqli $databaseConnectionObject
- * @param Doomguy $userObj
- */
- public function __construct(mysqli $databaseConnectionObject, Doomguy $userObj) {
- $this->_DB = $databaseConnectionObject;
- $this->_User = $userObj;
- $this->_setDefaults();
- }
- /**
- * Set the to work with collection id
- *
- * @param string $collectionId Number
- */
- public function setCollection(string $collectionId): void {
- if(!empty($collectionId)) {
- $this->_collectionId = $collectionId;
- }
- }
- /**
- * Set the following options which can be used in DB queries
- * array(
- * 'limit' => RESULTS_PER_PAGE,
- * 'offset' => (RESULTS_PER_PAGE * ($_curPage-1)),
- * 'orderby' => $_sort,
- * 'sortDirection' => $_sortDirection
- * );
- *
- * @param array $options
- */
- public function setQueryOptions(array $options): void {
- if(!isset($options['limit'])) $options['limit'] = 5;
- if(!isset($options['offset'])) $options['offset'] = false;
- if(!isset($options['sort'])) $options['sort'] = false;
- if(!isset($options['sortDirection'])) $options['sortDirection'] = false;
- $this->_queryOptions = $options;
- }
- /**
- * Get all available collections for display based on current user
- *
- * @param string $selections Number of selections
- * @param string $entries Number of entries
- * @param string $search Search string to search for
- * @return array
- */
- public function getLatest(string $selections, string $entries, string $search = ''): array {
- $ret = array();
- $queryStr = "SELECT `c`.`id`, `c`.`name`, `c`.`description`, `c`.`created`,
- `c`.`owner`, `c`.`group`, `c`.`rights`,
- `u`.`name` AS username, `g`.`name` AS groupname
- FROM `".DB_PREFIX."_collection` AS c
- LEFT JOIN `".DB_PREFIX."_user` AS u ON `c`.`owner` = `u`.`id`
- LEFT JOIN `".DB_PREFIX."_group` AS g ON `c`.`group` = `g`.`id`
- WHERE ".$this->_User->getSQLRightsString("read", "c")."
- ORDER BY `c`.`name`
- LIMIT $selections";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- $_mObj = new Mancubus($this->_DB,$this->_User);
- $_mObj->setCollection($result['id']);
- if(!empty($search)) {
- require_once 'lib/trite.class.php';
- $_colObj = new Trite($this->_DB,$this->_User);
- $_colObj->load($result['id']);
- $_fd = $_colObj->getCollectionFields();
- $_defSearchField = $_colObj->param('defaultSearchField');
- $_mObj->setQueryOptions(array(
- 'limit' => $entries,
- 'sortDirection' => $_colObj->param('defaultSortOrder'),
- 'sort' => $_colObj->param('defaultSortField')
- ));
- $_defSearchField = $_colObj->param('defaultSearchField');
- if(!empty($_defSearchField)) {
- $result['entries'] = $_mObj->getEntries(
- array(
- 0 => array(
- 'colName' => $_defSearchField,
- 'colValue' => $search,
- 'fieldData' => $_fd[$_defSearchField]
- )
- )
- );
- }
- else {
- Summoner::sysLog("[WARN] ".__METHOD__." missing default search field for collectionid: ".$result['id']);
- }
- }
- else {
- $result['entries'] = $_mObj->getEntries();
- }
- $ret[$result['id']] = $result;
- unset($_mObj);
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- return $ret;
- }
- /**
- * Get entries for loaded collection limited by search
- * and already set query options
- *
- * array[0] => array(
- * 'colName' => 'column name to search in',
- * 'colValue' => 'Value to search for',
- * 'fieldData' => field data from Trite->getCollectionFields()
- * 'exactTagMatch' => true to make a binary compare. false for match against search
- * )
- *
- * return array(
- * 'results' => array(),
- * 'amount' => int,
- * 'ids' => array()
- * )
- *
- * @param array $searchData
- * @return array
- */
- public function getEntries(array $searchData = array()): array {
- $ret = array();
- if(!empty($this->_collectionId)) {
- // split since part of it is used later
- $querySelect = "SELECT *";
- $queryFrom = " FROM `".DB_PREFIX."_collection_entry_".$this->_DB->real_escape_string($this->_collectionId)."` AS t";
- $queryJoin = '';
- $queryWhere = " WHERE ".$this->_User->getSQLRightsString("read", "t")."";
- $_isFulltext = false;
- if(!empty($searchData)) {
- // this search supports fulltext search and number <> search.
- // also can search in the entry2lookup table.
- // not perfect but it works
- foreach($searchData as $k=>$sd) {
- if(!isset($sd['colName']) || !isset($sd['colValue']) || empty($sd['colValue'])) continue;
- if($sd['fieldData']['searchtype'] == "tag") {
- $_isFulltext = true;
- $queryJoin = " LEFT JOIN `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_collectionId)."` AS e2l ON e2l.fk_entry=t.id";
- $queryWhere .= " AND e2l.fk_field = '".$this->_DB->real_escape_string($sd['fieldData']['id'])."'";
- if(isset($sd['exactTagMatch']) && $sd['exactTagMatch'] === true) {
- $queryWhere .= " AND e2l.value = BINARY '".$this->_DB->real_escape_string($sd['colValue'])."'";
- $_isFulltext = false;
- }
- else {
- $queryWhere .= " AND MATCH (e2l.value) AGAINST ('".$this->_DB->real_escape_string($sd['colValue'])."' IN BOOLEAN MODE)";
- }
- }
- elseif ($sd['fieldData']['searchtype'] == "entrySingleNum" && strstr($sd['colValue'],'<')) {
- $_s = str_replace('<','',$sd['colValue']);
- $queryWhere .= " AND `t`.`".$this->_DB->real_escape_string($sd['colName'])."` < ".(int)$_s."";
- }
- elseif ($sd['fieldData']['searchtype'] == "entrySingleNum" && strstr($sd['colValue'],'>')) {
- $_s = str_replace('>','',$sd['colValue']);
- $queryWhere .= " AND `t`.`".$this->_DB->real_escape_string($sd['colName'])."` > ".(int)$_s."";
- }
- elseif($sd['fieldData']['searchtype'] == "entryText") {
- $_isFulltext = true;
- $queryWhere .= " AND MATCH (`t`.`".$this->_DB->real_escape_string($sd['colName'])."`)
- AGAINST ('".$this->_DB->real_escape_string($sd['colValue'])."' IN BOOLEAN MODE)";
- }
- else {
- $queryWhere .= " AND `t`.`".$this->_DB->real_escape_string($sd['colName'])."` = '".$this->_DB->real_escape_string($sd['colValue'])."'";
- }
- }
- }
- $queryOrder = '';
- if(!$_isFulltext) { // fulltext do not order. Which results in ordering be relevance of the match
- $queryOrder = " ORDER BY";
- if (!empty($this->_queryOptions['sort'])) {
- $queryOrder .= ' t.'.$this->_queryOptions['sort'];
- }
- else {
- $queryOrder .= " t.created";
- }
- if (!empty($this->_queryOptions['sortDirection'])) {
- $queryOrder .= ' '.$this->_queryOptions['sortDirection'];
- }
- else {
- $queryOrder .= " ASC";
- }
- }
- $queryLimit = '';
- if(!empty($this->_queryOptions['limit'])) {
- $queryLimit .= " LIMIT ".$this->_queryOptions['limit'];
- # offset can be 0
- if($this->_queryOptions['offset'] !== false) {
- $queryLimit .= " OFFSET ".$this->_queryOptions['offset'];
- }
- }
- $queryStr = $querySelect.$queryFrom.$queryJoin.$queryWhere.$queryOrder.$queryLimit;
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- $_entryFields = $this->_getEntryFields();
- while(($result = $query->fetch_assoc()) != false) {
- $result = $this->_mergeEntryWithFields($result, $_entryFields);
- $ret['results'][$result['id']] = $result;
- $ret['ids'][] = $result['id'];
- }
- $queryStrCount = "SELECT COUNT(t.id) AS amount ".$queryFrom.$queryJoin.$queryWhere;
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStrCount));
- $query = $this->_DB->query($queryStrCount);
- $result = $query->fetch_assoc();
- $ret['amount'] = $result['amount'];
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- }
- return $ret;
- }
- /**
- * Retrieve all the data needed to display the entry for given entryId
- *
- * @param string $entryId Number
- * @return array
- */
- public function getEntry(string $entryId): array {
- $ret = array();
- if(!empty($this->_collectionId) && !empty($entryId)) {
- $queryStr = "SELECT *
- FROM `".DB_PREFIX."_collection_entry_".$this->_DB->real_escape_string($this->_collectionId)."`
- WHERE ".$this->_User->getSQLRightsString()."
- AND `id` = '".$this->_DB->real_escape_string($entryId)."'";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- $_entryFields = $this->_getEntryFields();
- if(($result = $query->fetch_assoc()) != false) {
- $ret = $this->_mergeEntryWithFields($result, $_entryFields);
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- }
- return $ret;
- }
- /**
- * Return the storage info for loaded collection
- * Used by API
- *
- * @return array
- */
- public function getEntryStructure(): array {
- $ret = array();
- $_entryFields = $this->_getEntryFields();
- $ret = $this->_mergeEntryWithFields($ret, $_entryFields);
- return $ret;
- }
- /**
- * Load the fields from the loaded collection
- *
- * @return array
- */
- private function _getEntryFields(): array {
- if(!empty($this->_cacheEntryFields)) {
- return $this->_cacheEntryFields;
- }
- if(!empty($this->_collectionId)) {
- $queryStr = "SELECT `cf`.`fk_field_id` AS id, `sf`.`type`, `sf`.`displayname`, `sf`.`identifier`,
- `sf`.`value` AS preValue, `sf`.`apiinfo` , `sf`.`searchtype`
- FROM `".DB_PREFIX."_collection_fields_".$this->_DB->real_escape_string($this->_collectionId)."` AS cf
- LEFT JOIN `".DB_PREFIX."_sys_fields` AS sf ON `cf`.`fk_field_id` = `sf`.`id`
- ORDER BY `cf`.`sort`";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- $this->_cacheEntryFields[$result['id']] = $result;
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- }
- return $this->_cacheEntryFields;
- }
- /**
- * Merge the loaded information from collection_entry with the given
- * configured fields
- *
- * @param array $entryData Loaded entry
- * @param array $entryFields Loaded fields
- * @return mixed
- */
- private function _mergeEntryWithFields(array $entryData, array $entryFields): array {
- if(!empty($entryFields)) {
- foreach($entryFields as $f) {
- $_mnValue = '_loadFieldValue_'.$f['type'];
- if(isset($entryData[$f['identifier']])) {
- $f['value'] = $entryData[$f['identifier']];
- unset($entryData[$f['identifier']]);
- } elseif(method_exists($this, $_mnValue) && isset($entryData['id'])) {
- $f['value'] = $this->$_mnValue($entryData['id'], $f);
- }
- $_mnSelectionValue = '_loadFieldSelection_'.$f['type'];
- if(method_exists($this, $_mnSelectionValue) && isset($f['preValue'])) {
- $f['preValue'] = $this->$_mnSelectionValue($f['preValue']);
- }
- $entryData['fields'][$f['identifier']] = $f;
- }
- }
- return $entryData;
- }
- /**
- * Load the values for given $entryId for $fieldData
- * lookup function for field type lookupmultiple
- *
- * @param string $entryId Number
- * @param array $fieldData
- * @return array
- */
- private function _loadFieldValue_lookupmultiple(string $entryId, array $fieldData): array {
- $ret = array();
- if(!empty($entryId) && !empty($fieldData) && !empty($this->_collectionId)) {
- // avoid db query for each wanted value
- if(isset($this->_cacheLookupValuesForEntry[$this->_collectionId])) {
- if(isset($this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']])) {
- $ret = $this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']];
- }
- }
- else {
- $queryStr = "SELECT `fk_field`, `value`, `fk_entry`
- FROM `".DB_PREFIX."_collection_entry2lookup_".$this->_DB->real_escape_string($this->_collectionId)."`";
- if(QUERY_DEBUG) Summoner::sysLog("[QUERY] ".__METHOD__." query: ".Summoner::cleanForLog($queryStr));
- try {
- $query = $this->_DB->query($queryStr);
- if($query !== false && $query->num_rows > 0) {
- while(($result = $query->fetch_assoc()) != false) {
- $this->_cacheLookupValuesForEntry[$this->_collectionId][$result['fk_entry']][$result['fk_field']][$result['value']] = $result['value'];
- }
- }
- }
- catch (Exception $e) {
- Summoner::sysLog("[ERROR] ".__METHOD__." mysql catch: ".$e->getMessage());
- }
- if(isset($this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']])) {
- $ret = $this->_cacheLookupValuesForEntry[$this->_collectionId][$entryId][$fieldData['id']];
- }
- }
- }
- return $ret;
- }
- /**
- * Get the single upload file from storage location
- * lookup function for field type upload
- *
- * @param string $entryId Number
- * @param array $fieldData
- * @return string
- */
- private function _loadFieldValue_upload(string $entryId, array $fieldData): string {
- $ret = "";
- if(!empty($entryId) && !empty($fieldData) && !empty($this->_collectionId)) {
- $uploadedFile = glob(PATH_STORAGE.'/'.$this->_collectionId.'/'.$entryId.'/'.$fieldData['identifier'].'-*');
- if(!empty($uploadedFile)) {
- foreach ($uploadedFile as $f) {
- $ret = basename($f);
- break;
- }
- }
- }
- return $ret;
- }
- /**
- * Get the multiple upload files from storage location
- * lookup function for field type upload_multiple
- *
- * @param string $entryId Number
- * @param array $fieldData
- * @return array
- */
- private function _loadFieldValue_upload_multiple(string $entryId, array $fieldData): array {
- $ret = array();
- if(!empty($entryId) && !empty($fieldData) && !empty($this->_collectionId)) {
- $uploadedFile = glob(PATH_STORAGE.'/'.$this->_collectionId.'/'.$entryId.'/'.$fieldData['identifier'].'-*');
- if(!empty($uploadedFile)) {
- foreach ($uploadedFile as $f) {
- $ret[] = basename($f);
- }
- }
- }
- return $ret;
- }
- /**
- * Load and prepare the value for a selection field
- *
- * @param string $data
- * @return array
- */
- private function _loadFieldSelection_selection(string $data): array {
- $ret = array();
- if(is_string($data)) {
- if(strstr($data, ',')) {
- $ret = explode(',',$data);
- }
- else {
- $ret[] = $data;
- }
- }
- return $ret;
- }
- /**
- * set some defaults by init of the class
- *
- * @return void
- */
- private function _setDefaults(): void {
- // default query options
- $options['limit'] = 5;
- $options['offset'] = false;
- $options['sort'] = false;
- $options['sortDirection'] = false;
- $this->setQueryOptions($options);
- }
- }
|