<br /> <p> <b>Documentary Sources:</b> <ul> <?php // find Reports related to Events realted to this address $records_eventreports = recordSearch_2('[{"t":"15"},{"relatedfrom:14":[{"t":"14"},{"related_to:12":"' . $recID . '"} ] }]'); // Report (15) -> DA Report (13) <- Place Role (16) -> Address $records_eventreports2 = recordSearch_2('[{"t":"15"},{"linked_to:13:78":[{"t":"13"},{"linkedfrom:16:78":[{"t":"16"},{"linked_to:12:90":"' . $recID . '"} ] }] }] '); if (count($records_eventreports2['records']) > 0) { $records_eventreports['records'] = mergeRecordSets($records_eventreports['records'], $records_eventreports2['records']); $records_eventreports['order'] = array_merge($records_eventreports['order'], array_keys($records_eventreports2['records'])); $records_eventreports['order'] = array_unique($records_eventreports['order']); } if (count($records_eventreports['records']) > 0) { foreach ($records_eventreports['order'] as $repID) { //getFieldValue($records_eventreports, $repID, DT_ORIGINAL_RECORD_ID) //find DA Report name $da_report = ''; $da_repID = getFieldValue($records_eventreports, $repID, DT_REPORT_DALINK); if ($da_repID > 0) { $da_report = recordSearch_2('ids:' . $da_repID); $da_report = getFieldValue($da_report, 0, DT_NAME); } ?> <li>
/** * put your comment there... * * @param mixed $system * @param mixed $params * * FOR RULES * rules - rules queries - to search related records on server side * getrelrecs (=1) - search relationship records (along with related) on server side * topids - list of records ids, it is used to compose 'parentquery' parameter to use in rules (@todo - replace with new rules algorithm) * * INTERNAL/recursive * parentquery - sql expression to substiture in rule query * sql - sql expression to execute (used as recursive parameters to search relationship records) * * SEARCH parameters that are used to compose sql expression * q - query string (old mode) or json array (new mode) * w (=all|bookmark a|b) - search among all or bookmarked records * limit - limit for sql query is set explicitely on client side * offset - offset parameter value for sql query * s - sort order * * OUTPUT parameters * vo (=h3) - output format in h3 for backward capability (for detail=ids only) * needall (=1) - by default it returns only first 3000, to return all set it to 1, * it is set to 1 for server-side rules searches * publiconly (=1) - ignore current user and returns only public records * * detail (former 'f') - ids - only record ids * header - record header * timemap - record header + timemap details * detail - record header + all details * structure - record header + all details + record type structure (for editing) - NOT USED * * CLIENT SIDE * id - unque id to sync with client side * source - id of html element that is originator of this search * qname - original name of saved search (for messaging) */ function recordSearch($system, $params) { //for error message $savedSearchName = @$params['qname'] ? "Saved search: " . $params['qname'] . "<br>" : ""; if (!@$params['detail']) { $params['detail'] = @$params['f']; //backward capability } $istimemap_request = @$params['detail'] == 'timemap'; $istimemap_counter = 0; //total records with timemap data $fieldtypes_ids = null; if ($istimemap_request) { //get date,year and geo fields from structure $fieldtypes_ids = dbs_GetDetailTypes($system, array('date', 'year', 'geo'), 3); if ($fieldtypes_ids == null || count($fieldtypes_ids) == 0) { $fieldtypes_ids = array(DT_GEO_OBJECT, DT_DATE, DT_START_DATE, DT_END_DATE); //9,10,11,28'; } $fieldtypes_ids = implode(',', $fieldtypes_ids); } else { if (!in_array(@$params['detail'], array('header', 'timemap', 'detail', 'structure'))) { //specific set of detail fields if (is_array($params['detail'])) { $fieldtypes_ids = $params['detail']; } else { $fieldtypes_ids = explode(',', $params['detail']); } if (is_array($fieldtypes_ids) && (count($fieldtypes_ids) > 1 || is_numeric($fieldtypes_ids[0]))) { $fieldtypes_ids = implode(',', $fieldtypes_ids); $params['detail'] = 'detail'; } else { $fieldtypes_ids = null; $params['detail'] = 'ids'; } } } $is_ids_only = 'ids' == $params['detail']; $return_h3_format = @$params['vo'] == 'h3' && $is_ids_only; if (null == $system) { $system = new System(); if (!$system->init(@$_REQUEST['db'])) { $response = $system->getError(); if ($return_h3_format) { $response['error'] = $response['message']; } return $response; } } $mysqli = $system->get_mysqli(); $currentUser = $system->getCurrentUser(); if ($system->get_user_id() < 1) { $params['w'] = 'all'; //does not allow to search bookmarks if not logged in } if ($is_ids_only) { $select_clause = 'select SQL_CALC_FOUND_ROWS DISTINCT rec_ID '; } else { $select_clause = 'select SQL_CALC_FOUND_ROWS DISTINCT ' . 'bkm_ID,' . 'bkm_UGrpID,' . 'rec_ID,' . 'rec_URL,' . 'rec_RecTypeID,' . 'rec_Title,' . 'rec_OwnerUGrpID,' . 'rec_NonOwnerVisibility,' . 'bkm_PwdReminder '; /*.'rec_URLLastVerified,' .'rec_URLErrorMessage,' .'bkm_PwdReminder ';*/ } if ($currentUser && @$currentUser['ugr_ID'] > 0) { $currUserID = $currentUser['ugr_ID']; } else { $currUserID = 0; $params['w'] = 'all'; } if (@$params['topids']) { //if topids are defined we use them as starting point for following rule query // it is used for incremental client side only //@todo - implement it in different way - substitute topids to query json as predicate ids: $query_top = array(); if (strcasecmp(@$params['w'], 'B') == 0 || strcasecmp(@$params['w'], 'bookmark') == 0) { $query_top['from'] = 'FROM usrBookmarks TOPBKMK LEFT JOIN Records TOPBIBLIO ON bkm_recID=rec_ID '; } else { $query_top['from'] = 'FROM Records TOPBIBLIO LEFT JOIN usrBookmarks TOPBKMK ON bkm_recID=rec_ID and bkm_UGrpID=' . $currUserID . ' '; } $query_top['where'] = "(TOPBIBLIO.rec_ID in (" . $params['topids'] . "))"; $query_top['sort'] = ''; $query_top['limit'] = ''; $query_top['offset'] = ''; $params['parentquery'] = $query_top; //parentquery parameter is used in get_sql_query_clauses } else { if (@$params['rules']) { //special case - server side operation // rules - JSON array the same as stored in saved searches table if (is_array(@$params['rules'])) { $rules_tree = $params['rules']; } else { $rules_tree = json_decode($params['rules'], true); } $flat_rules = array(); $flat_rules[0] = array(); //create flat rule array $rules = _createFlatRule($flat_rules, $rules_tree, 0); //find result for main query unset($params['rules']); if (@$params['limit']) { unset($params['limit']); } if (@$params['offset']) { unset($params['offset']); } if (@$params['vo']) { unset($params['vo']); } $params['needall'] = 1; //return all records $resSearch = recordSearch($system, $params); $keepMainSet = true; if ($keepMainSet) { //find main query results $fin_result = $resSearch; //main result set $flat_rules[0]['results'] = $is_ids_only ? $fin_result['data']['records'] : array_keys($fin_result['data']['records']); //get ids } else { //empty main result set //remove from $fin_result! but keep in $flat_rules[0]['results']? } $is_get_relation_records = @$params['getrelrecs'] == 1; //get all related and relationship records foreach ($flat_rules as $idx => $rule) { if ($idx == 0) { continue; } $is_last = @$rule['islast'] == 1; //create request $params['q'] = $rule['query']; $parent_ids = $flat_rules[$rule['parent']]['results']; //list of record ids of parent resultset $rule['results'] = array(); //reset //split by 3000 - search based on parent ids (max 3000) $k = 0; while ($k < count($parent_ids)) { //$need_details2 = $need_details && ($is_get_relation_records || $is_last); $params3 = $params; $params3['topids'] = implode(",", array_slice($parent_ids, $k, 3000)); if (!$is_last) { //($is_get_relation_records || //$params3['detail'] = 'ids'; //no need in details for preliminary results ??????? } $response = recordSearch($system, $params3); if ($response['status'] == HEURIST_OK) { //merge with final results if ($is_ids_only) { $fin_result['data']['records'] = array_merge_unique($fin_result['data']['records'], $response['data']['records']); } else { $fin_result['data']['records'] = mergeRecordSets($fin_result['data']['records'], $response['data']['records']); $fin_result['data']['order'] = array_merge($fin_result['data']['order'], array_keys($response['data']['records'])); foreach (array_keys($response['data']['records']) as $rt) { $rectype_id = @$rt['4']; if ($rectype_id) { /*if(@$fin_result['data']['rectypes'][$rectype_id]){ $fin_result['data']['rectypes'][$rectype_id]++; }else{ $fin_result['data']['rectypes'][$rectype_id]=1; }*/ if (!array_key_exists($rectype_id, $fin_result['data']['rectypes'])) { $fin_result['data']['rectypes'][$rectype_id] = 1; } } } } if (!$is_last) { //add top ids for next level $flat_rules[$idx]['results'] = array_merge_unique($flat_rules[$idx]['results'], $is_ids_only ? $response['data']['records'] : array_keys($response['data']['records'])); } if ($is_get_relation_records && (strpos($params3['q'], "related_to") > 0 || strpos($params3['q'], "relatedfrom") > 0)) { //find relation records (recType=1) //create query to search related records if (strcasecmp(@$params3['w'], 'B') == 0 || strcasecmp(@$params3['w'], 'bookmark') == 0) { $from = 'FROM usrBookmarks TOPBKMK LEFT JOIN Records TOPBIBLIO ON bkm_recID=rec_ID '; } else { $from = 'FROM Records TOPBIBLIO LEFT JOIN usrBookmarks TOPBKMK ON bkm_recID=rec_ID and bkm_UGrpID=' . $currUserID . ' '; } if (strpos($params3['q'], "related_to") > 0) { $fld2 = "rl_SourceID"; $fld1 = "rl_TargetID"; } else { $fld1 = "rl_SourceID"; $fld2 = "rl_TargetID"; } $where = "WHERE (TOPBIBLIO.rec_ID in (select rl_RelationID from recLinks where (rl_RelationID is not null) and {$fld1} in (" . $params3['topids'] . ") and {$fld2} in (" . implode(",", $is_ids_only ? $response['data']['records'] : array_keys($response['data']['records'])) . ")))"; $params2 = $params3; unset($params2['topids']); unset($params2['q']); $params2['sql'] = $select_clause . $from . $where; $response = recordSearch($system, $params2); //search for relationship records if ($response['status'] == HEURIST_OK) { if (!@$fin_result['data']['relationship']) { $fin_result['data']['relationship'] = array(); } if ($is_ids_only) { $fin_result['data']['relationship'] = array_merge_unique($fin_result['data']['relationship'], $response['data']['records']); } else { $fin_result['data']['relationship'] = mergeRecordSets($fin_result['data']['relationship'], $response['data']['records']); } /*merge with final results if($is_ids_only){ $fin_result['data']['records'] = array_merge($fin_result['data']['records'], $response['data']['records']); }else{ $fin_result['data']['records'] = mergeRecordSets($fin_result['data']['records'], $response['data']['records']); $fin_result['data']['order'] = array_merge($fin_result['data']['order'], array_keys($response['data']['records'])); $fin_result['data']['rectypes'][1] = 1; } */ } } //$is_get_relation_records } else { //@todo terminate execution and return error } $k = $k + 3000; } //while chunks } //for rules if ($is_ids_only) { //$fin_result['data']['records'] = array_unique($fin_result['data']['records']); } $fin_result['data']['count'] = count($fin_result['data']['records']); if ($return_h3_format) { $fin_result = array("resultCount" => $fin_result['data']['count'], "recordCount" => $fin_result['data']['count'], "recIDs" => implode(",", $fin_result['data']['records'])); } //@todo - assign if size less than 3000? only $fin_result['data']['mainset'] = $flat_rules[0]['results']; return $fin_result; } } //END RULES $chunk_size = PHP_INT_MAX; if (@$params['sql']) { $query = $params['sql']; } else { $is_mode_json = false; if (@$params['q']) { if (is_array(@$params['q'])) { $query_json = $params['q']; //DEBUG error_log('Q='.print_r($params['q'],true)); } else { $query_json = json_decode(@$params['q'], true); } if (is_array($query_json) && count($query_json) > 0) { $params['q'] = $query_json; $is_mode_json = true; } } else { return $system->addError(HEURIST_INVALID_REQUEST, $savedSearchName . "Invalid search request. Missed query parameter 'q'"); } if ($is_mode_json) { $aquery = get_sql_query_clauses_NEW($mysqli, $params, $currentUser); } else { $aquery = get_sql_query_clauses($mysqli, $params, $currentUser); //!!!! IMPORTANT CALL OR compose_sql_query at once } if ($is_ids_only && @$params['needall']) { $chunk_size = PHP_INT_MAX; $aquery["limit"] = ''; } else { $chunk_size = $system->user_GetPreference('search_detail_limit'); //limit for map/timemap output } if (!isset($aquery["where"]) || trim($aquery["where"]) === '') { return $system->addError(HEURIST_DB_ERROR, "Invalid search request; unable to construct valid SQL query", null); } $query = $select_clause . $aquery["from"] . " WHERE " . $aquery["where"] . $aquery["sort"] . $aquery["limit"] . $aquery["offset"]; //error_log($is_mode_json.' '.$query); /* DEBUG if($params['q']=='doerror'){ //force error $query ='abracadabra'; } */ } $res = $mysqli->query($query); if (!$res) { $response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error', $mysqli->error); } else { $fres = $mysqli->query('select found_rows()'); if (!$fres) { $response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error (retrieving number of records)', $mysqli->error); } else { $total_count_rows = $fres->fetch_row(); $total_count_rows = $total_count_rows[0]; $fres->close(); if ($is_ids_only) { //------------------------ LOAD and RETURN only IDS $records = array(); while ($row = $res->fetch_row()) { array_push($records, (int) $row[0]); } $res->close(); if (@$params['vo'] == 'h3') { //output version $response = array('resultCount' => $total_count_rows, 'recordCount' => count($records), 'recIDs' => implode(',', $records)); } else { $response = array('status' => HEURIST_OK, 'data' => array('queryid' => @$params['id'], 'count' => $total_count_rows, 'offset' => get_offset($params), 'reccount' => count($records), 'records' => $records)); } } else { //---------------------------------- // read all field names $_flds = $res->fetch_fields(); $fields = array(); foreach ($_flds as $fld) { array_push($fields, $fld->name); } array_push($fields, 'rec_ThumbnailURL'); //array_push($fields, 'rec_Icon'); //last one -icon ID $rectype_structures = array(); $rectypes = array(); $records = array(); $order = array(); // load all records while ($row = $res->fetch_row()) { //3000 maximal allowed chunk array_push($row, $fieldtypes_ids ? '' : fileGetThumbnailURL($system, $row[2])); //array_push( $row, $row[4] ); //by default icon if record type ID $records[$row[2]] = $row; array_push($order, $row[2]); if (!@$rectypes[$row[4]]) { $rectypes[$row[4]] = 1; } } $res->close(); if (($istimemap_request || $params['detail'] == 'detail' || $params['detail'] == 'structure') && count($records) > 0) { //search for specific details if (!$fieldtypes_ids && $fieldtypes_ids != '') { $detail_query = 'select dtl_RecID,' . 'dtl_DetailTypeID,' . 'dtl_Value,' . 'AsWKT(dtl_Geo), 0, 0, 0 ' . 'from recDetails where dtl_RecID in (' . join(',', array_keys($records)) . ') ' . ' and dtl_DetailTypeID in (' . $fieldtypes_ids . ')'; } else { $detail_query = 'select dtl_RecID,' . 'dtl_DetailTypeID,' . 'dtl_Value,' . 'AsWKT(dtl_Geo),' . 'dtl_UploadedFileID,' . 'recUploadedFiles.ulf_ObfuscatedFileID,' . 'recUploadedFiles.ulf_Parameters ' . 'from recDetails left join recUploadedFiles on ulf_ID = dtl_UploadedFileID where dtl_RecID in (' . join(',', array_keys($records)) . ')'; } // @todo - we may use getAllRecordDetails $res_det = $mysqli->query($detail_query); if (!$res_det) { $response = $system->addError(HEURIST_DB_ERROR, $savedSearchName . 'Search query error (retrieving details)', $mysqli->error); return $response; } else { while ($row = $res_det->fetch_row()) { $recID = array_shift($row); if (!array_key_exists('d', $records[$recID])) { $records[$recID]['d'] = array(); } $dtyID = $row[0]; $val = null; if ($row[2]) { $val = $row[1] . ' ' . $row[2]; //dtl_Geo @todo convert to JSON } else { if ($row[3]) { $val = array($row[4], $row[5]); //obfuscated value for fileid } else { if (@$row[1]) { $val = $row[1]; } } } if ($val) { if (!array_key_exists($dtyID, $records[$recID]['d'])) { $records[$recID]['d'][$dtyID] = array(); } array_push($records[$recID]['d'][$dtyID], $val); } } //while $res_det->close(); ///@todo // 1. optimize loop - include into main detail loop // 2. exit loop if more than 5000 geo enabled // 3. return geojson and timeline items //additional loop for timemap request //1. exclude records without timemap data //2. limit to $chunk_size if ($istimemap_request) { $tm_records = array(); $order = array(); $rectypes = array(); foreach ($records as $recID => $record) { if (is_array(@$record['d']) && count($record['d']) > 0) { //this record is time enabled if ($istimemap_counter < $chunk_size) { $tm_records[$recID] = $record; array_push($order, $recID); $rectypes[$record[4]] = 1; } $istimemap_counter++; } } $records = $tm_records; $total_count_rows = $istimemap_counter; } //$istimemap_request } } //$need_details $rectypes = array_keys($rectypes); if ($params['detail'] == 'structure' && count($rectypes) > 0) { //rarely used in editing.js //description of recordtype and used detail types $rectype_structures = dbs_GetRectypeStructures($system, $rectypes, 1); //no groups } //"query"=>$query, $response = array('status' => HEURIST_OK, 'data' => array('queryid' => @$params['id'], 'count' => $total_count_rows, 'offset' => get_offset($params), 'reccount' => count($records), 'fields' => $fields, 'records' => $records, 'order' => $order, 'rectypes' => $rectypes, 'structures' => $rectype_structures)); if ($fieldtypes_ids) { $response['data']['fields_detail'] = explode(',', $fieldtypes_ids); } } //$is_ids_only } } return $response; }